CODEWITHSUNDEEP
databasedatabase-design
ScottK
ScottK

Reputation: 277

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

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?

Upvotes: 2

Views: 16625

Answers (4)

Mubashir Waqar
Mubashir Waqar

Reputation: 1

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....

Upvotes: 0

Hassan Syed
Hassan Syed

Reputation: 20485

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)

Upvotes: 1

mst
mst

Reputation: 247

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.

Upvotes: 2

Jeepstone
Jeepstone

Reputation: 2601

Orders

orderid custid

Order Items

orderid itemnumber

Upvotes: 9

Related Questions