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

What is the best structure for an Orders table having O开发者_如何学运维rderNumber, ItemNumber and CustID that allows for 1 or more item numbers for each order number?

Orders

orderid custid

Order Items

orderid itemnumber

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.

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)

I design it as following.

1) Customers Id, Name, address...

2) Items Id, itemname, currentprice...

3) Orders Id, customerid, date...

4) OrderItems Id, orderid, itemid, quantity, unitprice...

5) payments id, orderid, paymentdate, amount, ref, payby....