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.


  1. User can order in 1 carton which equal to 192 units, or order in one pkt which equal to 24 units.

  2. 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.

  • I'm not sure if this is an example of the classic structure known as “Parts Explosion” or “Bill of Materials” but, if that's the case, you may find of help, for instance, this Q & A. – MDCCL Feb 14 at 15:32
  • Am i right if adding id_product to both pricing and unit table? Or any better suggestion or solution to achieve it.. – Crazy Feb 14 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.