Best structure for "Orders" table having multiple Items per order number

Question

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?

Solution 1

Orders

orderid custid

Order Items

orderid itemnumber

Solution 2

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.

Solution 3

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)