Is the order table divided into tables based on userid or orderid?

Is the order table divided into tables based on userid or orderid?

Reply content:

Is the order table divided into tables based on userid or orderid?

A user can have multiple orders, divided according to order ID

uiserid and orderid are one-to-many relationships, so the primary key of the order table is orderid and the foreign key is userid

The advantage of using orderid to divide tables is that the size of each table is not very different. For example, divide it according to time or create a new table when the number of orders reaches a certain value. The disadvantage is that userid is often used as the search condition when querying, so There will be a lot of union operations.

If userid is used, the situation is the opposite. The size of the table cannot be constant and will continue to grow over time. However, the advantage is that the query is a single table and will be very fast.

So my personal opinion: If nosql is not used for caching, it is recommended to use userid to separate tables. If you are pursuing performance, you can use memcache for caching. Even if you use orderid to split the table, the cache will be read directly during the second query, so the speed will be very fast. It will be friendly to users who frequently check orders.

Use orderid to divide the table

orderid is easy to store and the database load is relatively consistent. But data aggregation is more difficult.
userid is easy to aggregate. However, there will be hotspot distribution problems, resulting in inconsistent data table sizes.
Choose for yourself