table products id_product | name | .... 1 | abcd | .... --- table prices id_price | Amount | price_type | price 1 | 20 | Discount | 20 --- table unit id_unit | fromUnit | Factor | toUnit 1 | Cardboard | 8 | pkt 2 | pkt | 24 | units
I design a database for the commercial business. Basically, each product can have multiple product and price plans.
For example, item A can be sold in carton or pkt, and a default conversion value is specified in.
The user can order in a carton of 192 units or in a carton
which corresponds to 24 units.
When the user receives the article, the article may be in a different format. For example, order in pkt but received in carton.
I will always convert and save the received uom so I can track it in my inventory.
Any suggestions on how to design my database and link the table based on multiple unit cases and multiple product prices? Many Thanks.