MySQL Performance Gains By Splitting Tables

database-designMySQLschema

I am designing a database schema to be used with MySQL. The database needs to keep track of a users basket items and purchased items. I've thought about two ways I could do this but not sure which would be better.

1.Have one table

user_product

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

The user_product table stores details of any items that a user has in their basket and items the user has purchased. For purchased products a foreign key to a sales_order_id is added, for basket items no sales_order_id is added

2.Have two tables

user_basket

id (PK) | user_id (FK) | product_id (FK)

user_purchased

id (PK) | user_id (FK) | product_id (FK) | sales_order_id (FK)

Fairly self explanatory. Items that are pending in a users basket in the first, items they have purchased in the second with a foreign key to a sales order.

The first question I have is are both solutions 3NF? Im 99% sure they both are but if one isn't then I can eliminate it.

The second question is does one solution perform better than the other? I would prefer to go with solution 1, but worried that a lot of queries will be made to that one table. Would it be better to separate out to two tables like solution 2 or does it not really make a difference as the same number of queries will be made to the overall database irrespective of whether its two tables or one.

Best Answer

I'd vote for #2. Will you not need a item_price and a quantity in the user_purchased table? (for which order_details looks a better name, by the way.) Or other, extra columns for the user_basket table? I can easily see how the table will get wide, with a lot of nullable columns. MySQL has no CHECK constraint so how do you plan to enforce integrity?

And why have both kind of queries, the ones that search the Basket and the ones that search the Purchased, all trying to access one table, when you can avoid it?

Other advantages:

  • Narrower tables, which is usually good for indexing.
  • One of the 2 tables (the user_purchased) will have only INSERT operations and rarely DELETE or UPDATE and will be growing, so you will not have fragmentation there. With one table for all, which will be affected by all kind of operations, it will be hard to avoid fragmentation.
Related Question