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?
you need a item-count
Hassan Syed
2010-01-04 17:17:42
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
2010-01-04 17:24:13
@Hassan Syed - why would an item count be needed? This is easier to derive than maintain.
Chris Simpson
2010-01-04 17:25:11
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
2010-01-04 17:28:18
aha, classic database terminology misunderstanding. I suggest using "quantity" and not "count".
Chris Simpson
2010-01-04 17:32:38
Fair Enough, my mathematician colleague said the same :D Updated my answer. Btw why is count confusing ?
Hassan Syed
2010-01-04 17:36:43
I do need quantity in Order Items table.
ScottK
2010-01-04 17:38:17
:P choose the "correct" answer then, not just what people blindly upvote.
Hassan Syed
2010-01-04 17:46:30
@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
2010-01-04 17:49:13
@Chris - aha, the SQL count(*) clause, I see what you mean. Thank you for clarifying
Hassan Syed
2010-01-04 17:55:35
+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
2010-01-04 17:16:57
+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
2010-01-04 17:19:01