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

Question

I am trying to design a database that stores products that are sold in varying different units. Some are priced by product, some by volume, some by length, some by surface area. Possibly some by weight. It is not a simple matter of storing the price in one specific unit, and then making the conversions (volume does not convert to surface area).

My current thought is having a product table, a price table, and a unit table. A product has one price, and a price has a per-unit integer cost and one unit. The unit table would contain an entry for every possible unit that products can be sold in, with a single column just being a string containing the name of the unit.

Is this the right approach? Is there a better way to handle such a scenario?

Answer 1

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.