1

I am creating a billing software. I have created a database having 4 tables.

  • Category_Master
  • Product_Master
  • Customer_Master
  • Order_Details.

I am confused at creating rows in Order_Details. The reason is that, if a customer purchases 10 different items, then each items ProductCode should also be added to the Order_Details table.

So the thing is that do i need to create rows for each and every products or is their any other way to represent all ProductCode in a single cell.

Improve this question
1
  • u can save multiple productcodes as comma seperated or in Xml format in a single cell. Commented Oct 23, 2013 at 10:55

3 Answers 3

Reset to default
1

I would recommend you to slit your Order_Details into two tables:

OrderProduct

OrderID | ProductCode

Order_Details

OrderID | OtherParameter

Each product of the order should be a new row in OrderProduct table. This structure will allow you to store order details separate from Products, connected with this order. The OrderProduct table would contain only links of your products with the orders in relation of many to many. Joining of these tables would allow you to make any required Select queries.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hi, Thanks for the reply. In the OrderProduct table, same orderID will have more than one ProductCode. It should be added in the same cell or multiple cells with different Row name?
If a customer is buying 100 different products, then i need to create 100 different rows. Right.
@KaMaLMoHaN Suppose you have and order (ID=1) and the products of this order - Product1 (ID=1), Product2 (ID=2), Product3 (ID=3). In your OrderProduct you would have 3 records: 1|1, 1|2 and 1|3. If you then would have another order with products 1 and 3, then your table would additionaly contain 2 records: 2|1 and 2|3. And so on.
100 different rows and 2 columns. Now i am getting the idea. And OrderID can be duplicated. Is that right.
@KaMaLMoHaN Yes, your other orders could be linked with the products, which were in previous orders. I suppose you use a logic, where a single product has some quantity and different users can order it.
1

You need to create rows. Although there are alternatives that are technically possible, all of them are incredibly bad design and an uneducated hack at best.

Improve this answer

7 Comments

Hi thanks for the fast reply. That means i need to create 10 different rows?
If an order has 10 different products, normally you would create 10 rows, each with the same OrderID and a different ProductID.
Thanks for the reply. Now i got the idea.
Dear downvoter, please leave a comment so I can improve. Downvoting without comment is not helping anybody.
@nvoigt "Incredibly bad design" -> Way too general a statement to be true. It depends on how the data is used and storing the order items in an XML column will occasionally actually be a good idea. I think.
|
1

I would also suggest you to create one more table (Order_Products) to manage the products ordered under one order, So that you will be able to easily track the products ordered under one order. Managing multiple values using a single cell can be used if the multiple values are of a constant size, such as days of a week which can be managed by using a binary field, But in your case the number of products is a variable and so i prefer using another table for doing the same.

Thank you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.