views:

177

answers:

3

What is the best structure for an Orders table having OrderNumber, ItemNumber and CustID that allows for 1 or more item numbers for each order number?

+4  A: 

Orders

orderid custid

Order Items

orderid itemnumber

Jeepstone
you need a item-count
Hassan Syed
Why do you need an item-count? In a fully normalized database this is not required, but I'm listening to your logic too.
marcc
@Hassan Syed - why would an item count be needed? This is easier to derive than maintain.
Chris Simpson
so if a user wants 15 inkjet cartridges you would have 15 entries in the table ? I routinely see order of 100,000 items :D
Hassan Syed
aha, classic database terminology misunderstanding. I suggest using "quantity" and not "count".
Chris Simpson
Fair Enough, my mathematician colleague said the same :D Updated my answer. Btw why is count confusing ?
Hassan Syed
I do need quantity in Order Items table.
ScottK
:P choose the "correct" answer then, not just what people blindly upvote.
Hassan Syed
@Hassan Syed - because count is an aggregate. It looked like you wanted to hold the number of items on each order rather than calculate the count when it's needed. The other thing that was confusing about your answer was that the detail table is at the top although this is not immediately clear. This confused me and I imagine a few other people.
Chris Simpson
@Chris - aha, the SQL count(*) clause, I see what you mean. Thank you for clarifying
Hassan Syed
+1  A: 

basket/order - table

1 : order-id -- item-id -- item-count/Quantity

cust/order history table

2 : cust-id -- order-id -- status (when you find out who the cust is)

Hassan Syed
+1  A: 

I highly suggest you normalize this properly and use two tables: an Orders table which keeps a record for every order and an OrderItem table, which keeps the order key (referencing back to the Orders table), the item number and quantity as well as subtotal/total price. This way, if you also need some customization (discounts, packaging fees etc) you can do so easily.

mst
duplicate of my answer :/
Hassan Syed