|
I’m trying to make a simple shopping cart in PHP and am unsure of how to structure the database. I believe there are three tables that I need (but I could be wrong!):
1. products table
columns:
pn, name, base_price, category, images, tiered_prices, allowed_quantities, main_img, desc, weight, dimensions, qty_in_stock
column descriptions:
pn: the product part number
name: the product name
base_price: the base price of the item
category: the parent category for the item
images: serialized or json array of product images (probably between 2 and 5 images)
tiered_prices: serialized or json array. E.g. 10 items -> $1.00, 100 items -> $0.75, etc..
allowed_quantities: some items can only be purchased in certain quantities, such as 10, 100, ect.
main_img: the name of the main product image
desc: the product description
weight: the weight of the product
dimensions: serialized or json array containing width, height, depth
qty_in_stock: the number of these items in stock
2. cart table
columns:
id, pn, qty, user_id, date_added
column descriptions:
id: the primary key for the row (otherwise useless/unused)
pn: the part number of the product
qty: the quantity in the cart
user_id: the user id if logged in
date_added: the date the row was added. Rows older than, say, 30 days will be purged.
So, with this design, every item in the cart would have its own row in the the cart table. (The other method would be to serialize/encode the items/qtys in the cart and store them in one row).
3. orders
I’m really not sure how to do this table. I don’t think a row should exist for every item in the order, because that would be a TON of rows! Would it be better to serialize/json encode the pn’s & quantities of all the items purchased along with other relevant order information and store it all in a single row in the orders table?
Thanks for your advice!
|
