Database design for products with multiple units I am designing a database for retail business. There are some products that can be sold in multiple units, for example, pencils can be sold in ea and dozen, paper can be sold in sheet, ream, and canton. Basically, each product can be sold in more than one unit.

The software needs to supports


Can receive products from suppliers in many unit. Sometime we might order 1 pencil and the next time we order 2 boxes of pencil.
Can sell products in multiple unit, for example, we must be able to sell 1 box and 2 pencils in the same bill.
Can check the actual items in stock.


The following are my initial design.

Table Products
ProductId | Barcode | Name   | CurrentPriceId
1         | XXXX    | Pencil | 1

Table Prices
Id | Amount
1  | 0.49

Table Units
UnitId | Name
1      | Ea
2      | Box

Table UnitConverter
ProductId | FromUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils equals 1 box

Table Inventories
Id | ProductId | UnitId | Quantity | PurchasePrice
1  | 1         | 1      | 48       | 0.23

Table Invoices
Id | ProductId | UnitId | Quantity | PriceId
1  | 1         | 1      | 27       | 1 


Is there any flaws in my design? Is there anything that I miss?
Removed the pricing from this model, just to keep this example simple and precise! It is important to use Base Unit, that means you must maintain each SKU (Barcode) with base unit. Ofcourse, it is possible to do the purchases, store and sell with Base Unit or any other applicable unit based on Unit Conversion configuration.

Here is the design I would like to propose, please review and let me know your thoughts!

Table: Products
ProductId | Barcode | Name   | BaseUnitId
1         | XXXX    | Pencil | 1

Table: Units
UnitId | Name
1      | Each / Pieces
2      | Box

Table: UnitConversion
ProductId | BaseUnitId | Multiplier | ToUnitId |
1         | 1          | 24         | 2        | // 24 pencils in a box

//Better to store the Inventories in Base Unit. You can perform conversion on the fly only if the requirement arise.
Table: Inventories
Id | ProductId | UnitId | Quantity 
1  | 1         | 1          | 48                //In pieces

Table Invoices
Id | ProductId | UnitId | Quantity
1  | 1         | 2      | 1.5                   //Sold/Purchased 1.5 boxes that means 36=(1.5*24) pieces

//You can perform transaction against the products in any legitimate Unit (validate against UnitConversion table)