Correct way to design a shopping cart websites database

database-design

I am in a web applications course. My instructor really didn't like the layout of my database, saying it was incorrect in pretty much every way. Fast forward 2 months when I'm remaking the website in a new language. I can't for the life of me see what's wrong. What is the correct way of making this layout? I have to use these and only these tables.

regUsers:
un
addr
PK uID
email
etc

Links to orders on mutual PK uID

orders:
orderID
pk uID

Links to orderDetail on orderID

orderDetail:
PK orderID
total
items (list)
date
address

Each item in items links to products. Not sure how to write a formal link between a list of Product IDs to the product table in MS SQL server 2008

product:
PK prodectNo
Pname
notes
price
imgpath
stock

Which links to productType on productNo PK 

Product Type:
productNo
type

Best Answer

The main problem with your schema is the OrderDetail table: you have mixed data which is atomic (the date, the address) along with repeating data - the products ordered.

Your Orders table should be as follows

OrderId (primary key)
OrderNumber 
UserID (foreign key to users)
OrderDate

(address really belongs to the user)

whereas the OrderLines table should be as follows

OrderLine (primary key)
OrderId (foreign key to orders)
PartID (foreign key to products)
Quant
Price
Discount
Total (although this can be calculated as quant * price * (100 - discount) * 0.01)

Each order line has a total but there should not be a 'total' field in the orders table; instead, calculate this from the orderlines table.

The 'OrderLine' field might be redundant, if 'OrderId' and 'PartId' can be used as a composite primary key, which would mean that no part can appear twice in the same order. This might seem reasonable, but if you add fields such as 'supply date' (the date by which the customer wants to receive each part), you would have problems if a customer orders 100 widgets to be supplied by the end of December and another 100 widgets to be supplied by the end of January. The surrogate key (orderline) is probably the safest and easiest option.

Related Question