Correct way to design a shopping cart websites database

Question

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

Answer 1

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.

Answer 2

I'm going to try to layout the tables a little clearer, just so I'm sure we are on the same page.

regUsers( uID PK, un, addr, email, etc);
orders( uID PK,orderID);
orderDetail( PK orderID, total, items (list), date, address);
product( prodectNo PK, Pname, notes, price, imgpath, stock);
Product_Type( productNo, type);

If you had given SQL then I could have probably told you what your instructor disliked. For now I can only offer guesses.

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

Are you familiar with the concept of foreign keys? If the answer is "no", then this is probably something your professor didn't like. Taking your regUsers and orders table as an example, you may want to use something like the following:

CREATE TABLE regUsers(
 uID INT PRIMARY KEY,
 un VARCHAR,
 addr VARCHAR,
 email VARCHAR,
 etc VARCHAR);

CREATE TABLE orders(
 uID INT PRIMARY KEY REFERENCES regUsers(uID),
 orderID INT);

I selected VARCHAR somewhat arbitrarily because I don't know anything about the data you plan on storing. The important part is that the uID in orders REFERENCES regUsers(uID). This means that all uID values in your orders table must also exist in your regUsers table.

Another point of interest here: you claim that you want uID to be the PK of orders, but uID is also the PK of regUsers (its foreign key). This implies that every uID in regUsers can have at most ONE row in the orders table... is that what you intended? Recall that PKs are unique.

Which links to productType on productNo PK

Same issue with your Product Type table. If you are going to make product_no your PK for that table, then you can only have one row for each product_no; I don't think this is what you want. If it is what you want, and every product_no can have at most one type, then you might consider just storing the type in the product table and avoiding the Product Type table all together.

Another possible point, what did you mean by (list)? I did a quick search to see if there is a LIST data type in MS SQL server and found nothing. Are you planning on storing a list of strings in this column? If so, then it is a good candidate for a separate table all together.

Have you thought at all about the queries you would need to execute in order to get the information you need? What data types did you assign each column? In general, a good way to start designing a schema is to create an E/R diagram, but in this case you may need to do a little more research. I could be wrong here as well, and I would be glad to revise my response if you provide the actual SQL you are using to create the tables.

Answer 3

There is few things you should always remember whenever you create a database -

1. Use foreign keys.
2. Use proper table names so not only you any developer can understand that without any problem.
3. Use proper table column names.
4. Use proper column data types.
5. Use proper initials if you use same name for two different objects in database. Like if you use stored procedures for Orders table. Then you should use table name as "tbl_Orders" and stored procedure name as "prc_Orders".

An example for table columns -

Create table tbl_Orders
(
Order_ID int identity primary key,
Customer_ID int,
Order_Date date,
Order_Time time,
Ship_Date date,
Shipping_Method int,

foreign key (Cust_ID) references tbl_Cusomers(Cust_ID),
foreign key (Shipping_Method) references tbl_Shipping(Ship_ID)
)