Table Products id_product| name | .... 1 | abcd | .... --- Table Pricing id_price | qty | price_type | price 1 | 20 | discount | 20 --- Table Unit id_unit | fromUnit | factor | toUnit 1 | carton | 8 | pkt 2 | pkt | 24 | units
I am designing database for commercial business. Basically, each product may have multiple uom (unit[s] of meassurement) and pricing plans.
E.g. item A can be sold in carton or pkt (packet), and there will be a default uom to be converted to.
User can order in 1 carton which equal to 192 units, or order in one pkt which equal to 24 units.
When user receive the item, the item might be in different uom. E.g. ordered in pkt but received in carton.
I will always convert and store the received uom, so that I can track in my inventory.
Any suggestion on how can I design my database and link the table based on the cases for multiple uom and multiple pricing issue for product? Thanks.