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?