Suppose I have a table for purchase orders. One customer might buy many products. I need to store all these products and their relevant prices in a single record, such as an invoice format.
Asked
Active
Viewed 128 times
0
-
4DO NOT DO THIS!!!!!!!! Store each purchased product in a separate row but assign them a same key something like Order Number. – M.Ali May 27 '16 at 09:35
-
As M.Ali said that will become a really bad design then. what will you do if you need to update only a particular order? – bmsqldev May 27 '16 at 10:17
2 Answers
0
This is a typical example of an n-n relationship between customer and products.
Lets say 1 customer can have from 0 to N products and 1 products can be bought by 0 to N customers. You want to use a junction table to store every purchase orders.
This junction table may contain the id of the purchase, the id of the customer and the id of the product.
sapi
- 244
- 1
- 9
0
If you can change the db design, Prefer to create another table called PO_products that has the PO_Id as the foreign key from the PurchaseOrder table. This would be more flexible and the right design for your requirement.
If for some reason, you are hard pressed to store in a single cell (which I re-iterate is not a good design), you can make use of XMLType and store all of the products information as XML.
Note: Besides being bad design, there is a significant performance cost of storing the data as XML.
Piyg
- 224
- 2
- 10