ok, this is a pretty easy one (I have designed databases for a lot of e-commerce sites). Basic cart structure is as follows:
Tables:
USER: user_id, name, password
CART: cart_id, user_id, create_date (and anything else specific to one cart)
CART_ITEM: cart_item_id, cart_id, product_id, item_quantity, item_price (and anything else specific to a product in the cart)
PRODUCT: product_id, name, price, quantity_available (and anything else specific to a product)
The way it works is a user can have multiple carts associated with them. Carts are made up of multiple cart_item's. Cart_item's are made up of products and quantity. The price is stored there as well as in the product table for historical purposes (in case the product price changes, you have a record of what you sold it for in the past).
Say you have item1, item2, and item3 as products. You want 2 of item2 and 5 of item3 and one item1. This results in one row on the user table, one row on the cart table (for the indiviual cart, or order) and three rows on the cart_item table, each associated with the cart table through the cart_id on the cart_item table and with the product table through the product_id. The first cart_item row has item1 in it with a quantity of one, the second has item2 in it with a quantity of 2, and the third has item3 in it with a quantity of 5.
I know this seems like a lot of tables, but it is the standard way to normalize the data.
Make sense?
D
