3

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

  1. Can receive products from suppliers in many unit. Sometime we might order 1 pencil and the next time we order 2 boxes of pencil.
  2. Can sell products in multiple unit, for example, we must be able to sell 1 box and 2 pencils in the same bill.
  3. 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?

  • I think you're on the right track however this model only works well with discrete items. Depending upon the business and base model they may also sell units by weight or volume. For example a roll of cable or cloth might sell by the meter. i.e the roll has 100m of cable, and you sell 21.5 meters. Oil by the litre/gallon. Ham by the kg etc. Depending upon the model and how inventory is tracked you might deplete the original item rather than break it down. – Sir Swears-a-lot Feb 12 '17 at 20:32
  • Also Inventory might be tracked in different units that how it is sold. eg a supermarket might have 30 Hams, but each has a variable weight and is sold per kg. So they might deplete the inventory by 1 but the price is calculated per the item weight and not 1 x $n.nn. The same could be applied to fruit and vegetables. They might order by the crate but the stock is sold by weight or unit. In that case they might not bother trying to deplete the original unit at all. They just re-order when the crate is empty. – Sir Swears-a-lot Feb 12 '17 at 20:32
1

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)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.