Database design for products with multiple units

database-design

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?

Best Answer

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)

Related Solutions

Inventory Database Design Issue

I agree with TomTom on the merits of the Datamodel resource book - it will give you more insight than StackOverflow, and if memory serves, there's a fairly detailed discussion of exactly this scenario.

The design you've come up with is similar to what I've seen for other systems - what you call ProductVariation is often called "variant".

I'm a little confused about the role of SKU in your design - the acronym stands for "Stock Keeping Unit". As it's supposed to be unique and never change, I'd make that the primary key of your ProductVariation table, rather than ID.

If you're only ever going to sell your current product range, or are happy to refactor in future, you can leave it as is; alternatively, you could consider a design with "table per subclass". This would give you a schema along the lines of:

Product (id, name, categoryID, description)
- SizeVariant (SKU, productID, finish, width, thickness, Size, location, quantity)
- ColourVariant (SKU, productID, Colour, finish, location, quantity)
- NoVariant (SKU, productID, location, quantity)

Note how I've also put some of the attributes you've currently assigned to "Product" into the child tables.

This keeps the schema more "self describing", but adds a lot of work - only do that if you think the product catalogue will grow in the future, and you want to avoid EAV.

DB Design storing products sold in bulk, priced by vastly different units

There are two ways to do this. The fast, and the "academic".

In pseudocode (change to fit your database)

The fast/high performance way:

CREATE TABLE Products (
  ID INT
  , Name VARCHAR
  , PricePerKg DECIMAL NULL
  , PricePerMeter DECIMAL NULL
  ... etc...
)

While this is not pure 5NF, it is FAST because all lookups of products can be done with a single index seek. You should add a constraint that ensures that at least one of the price/unit is NOT NULL (easy with COALESCE or NVP). It is very easy to write code that will access the above.

The academic way

CREATE TABLE Product (
  ID INT
  , Name VARCHAR
)

CREATE TABLE ProductPrice (
  ID_Product INT NOT NULL FOREIGN KEY
  , ID_Unit INT NOT NULL FOREIGN KEY
  , PricePerUnit DECIMAL NOT NULL
)
PRIMARY KEY (ID_Product, ID_Unit)

CREATE TABLE Unit (
  ID_Unit NOT NULL PRIMARY KEY
  , Name VARCHAR
)

The latter requires up to 2 joins to grab the information. But it is of course more flexible. To make sure the last solution does not create bad quality data, you will want a trigger to enforce that when the last ProductPrice is deleted, the product goes with it (or you set a flag).

If the number of prices per unit is known to be small, there is really no reason to pick the latter solution. There are no awards for being academically correct.

Best Answer

Related Solutions

Inventory Database Design Issue

DB Design storing products sold in bulk, priced by vastly different units

Related Question