php - Multiple shops can be added to the system, and each shop has transaction records. How to design a table?

Question

My idea is that shop shop is a table used to save shop information.
Then there is a transaction table with the field shop_id, which stores the transaction record information of all shops.

Will such a table design have an impact when the amount of data is large? Is there a better way? For example, each store's transaction record is a table, but it feels difficult to implement because stores can be added dynamically. .

Answer 1

Mysql performs very well with thousands of records. This design can run the same, but if a store has ten orders on average, then the store and order data are mapped 10 times, which affects performance when the number of orders is quite large. . Database tables can also be added dynamically. So you can perform sub-table processing on order records. However, you cannot use the auto-incremented shop_id as a foreign key, because after you split the table, the auto-incremented id base returns to zero. Even if it can be set manually, it is very inconvenient, so it is best to generate the foreign key yourself to facilitate table splitting. There are many ways to generate unique IDs, and you can design one yourself.

Answer 2

My understanding is to design a shop table and a transaction table. The foreign key of the transaction table is the primary key of the shop table. In general, MySQL can still withstand a lot of pressure, and some open source can be used. The solution, or the order table is divided into regular tables, the key depends on how you implement it.

Correct what I said above. In fact, I rarely use foreign keys in development. Foreign keys are a double-edged sword. I suggest you take a look at why you should try to avoid using foreign keys in development