What's the best structure to have an Orders table getting OrderNumber, ItemNumber and CustID that enables for 1 or even more item amounts for every order number?


orderid custid

Order Products

orderid itemnumber

basket/order - table

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

cust/order history table

2 : cust-id -- order-id -- status (whenever you discover who the cust is)

I highly suggest you normalize this correctly and employ two tables: an Orders table which will keep an archive for each order as well as an OrderItem table, which will keep an order key (referencing to the Orders table), the product number and quantity in addition to subtotal/total cost. By doing this, should you likewise need some personalization (discount rates, packaging costs etc) that can be done easily.