SQL / PHP：表设计问题

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.