Many items in single order, SQL table design

Question

I got one order table, when user come to the store they could purchase more than one item for example. may i know what is the best practices to perform such record action ? is that each item order one row in order table or mix all into one row in order table ?

Let say, Customer A purchase 3 items from store. then will be 3 row in order table ? or 1 row in order table with all details and separate order with delimiter ?

My designed structure is like this::

tblOrder
    OrderID (Primary Key)
    UserID
    TotalPrice

tblOrderItem
    OrderItemID (Primary Key)
    OrderID (Referencing tblOrder)
    Quantity
    ItemID (Referencing tblItem)
    TotalPrice

Is this correct ?

Shaikh Farooque · Accepted Answer · 2012-05-10T05:07:20.020

1

I will suggest you to maintain 3 rows, because it will be easy for you to maintain further.

Updated:

@SLim, The structure is looking perfect for the situation now. You will proceed further with this.

edited May 10 '12 at 05:07

answered May 10 '12 at 05:00

Shaikh Farooque

2,620
1
19
33

something was edited =D can you please judge is that correct ? Thx =D – 1myb May 10 '12 at 05:01

score 1 · Answer 2 · answered May 10 '12 at 05:02

1

Without question, each order should have its own row in the database table.

Otherwise, for example, if you wanted to find all orders where a particular item was purchased -- how would you do it? It's much easier if there's a single item per row.

More than that, this is the approach taken by larger e-commerce sites. If you're interested in learning to develop software professionally you must learn the best practices.

Good luck!

answered May 10 '12 at 05:02

Kevin Bedell

13,254
10
78
114

Thank you for your suggestion =D That is... wondering how to make the application better =D – 1myb May 10 '12 at 05:06

Many items in single order, SQL table design

2 Answers2