Mysql – Which foreign keys do I use if they are repeated

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.

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!