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.


Links to orders on mutual PK uID

pk uID

Links to orderDetail on orderID

PK orderID
items (list)

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

PK prodectNo

Which links to productType on productNo PK

Product Type:

3 Answers 3


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)
UserID (foreign key to users)

(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)
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.

  • I think the composite primary key works even in the case you've described. It doesn't really make a difference for the layout when items are supposed to be dispatched. That's something for the programming layer to deal with, not the DB's.
    – DanMan
    Dec 6, 2014 at 16:10

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:

 addr VARCHAR,
 email VARCHAR,
 etc VARCHAR);

 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.


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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.