SQL/PHP: Table design question

Question

I have two tables, one called "cart" and one called "items". Now I want cart to be able to store a list of "items" and quantities. So I created another table "cart_items" that has a field for the id of the template, the id of the item and the quantity.

Is this good design, or should I add another field for the id of the "cart_item"? Or is there better way to do this all together? I'm just running in trouble when I try to update my cart my cart items don't update correctly. What I'm having to do is delete all the "cart_items" attached to a cart and then re-add each "cart_item" and I can't help but think there has to be a better way to do this.

Answer 1

That's very good design.

In ER (entity relationship) terms there is a many-to-many relationship between items and carts. By that I mean an item can appear in many different carts and a cart can contain many different items. "Many" in this context means "a variable number that can be more than one".

Your cart_items table is what's called a join table and you need it to model many-to-many relationships. Added a quantity field also makes sense as does putting it on cart_items.

You have two choices when it comes to the key:

1. Make (cart_id, item_id) the primary key. This is called a composite primary key; or
2. Creating an auto increment field called id. If you do this make (cart_id, item_id) a unique index to enforce uniqueness.

Either choice is valid. Personally I prefer (2). You should also have a unique index on (item_id, cart_id) in all likelihood.

Answer 2

Im not sure exactly how your tables would look, but something like below would be a good start.

Items 
+-- ID --+-- Description --+-- Etc --+
|       1|  Good item here | Buy one.|
|       2|    Another item | somethin|


Cart
+-- Cart ID --+-- Item ID --+-- Amt --+ 
|            1|            1|        4|
|            1|            2|        3|

Each cart has a unique ID, with each item stored in its own row. This way you can add or remove items one at a time, while keeping them linked, and also select all items from one cart at the same time.

Answer 3

One pointer I would give you is, instead of linking carts to items directly, that you copy all the relavent information from the item into another table, possibly cart_Items, and link that to the cart. This is useful so that you maintain what was added to the cart over time. Unless these are just quick throw away carts that you only need for a single session, you want to be able to have a history of exactly what the person put into their cart at that point in time, and not have their cart data change when somebody decides to change something about the item, or have the item disappear when the item is deleted.

Answer 4

What you are doing is correct as explained previously. The only thing that is missing is to use DELETE CASCADE and FOREIGN KEYS in your database.

This way when you delete a row in CART it will go to also delete it in CART_ITEM.