2

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?

  • are the ways products are sold known at design time or is it truly open ended? I.e. how much flexibiliy do you need? Could you create an exhaustive list of the way products are sold? – Thomas Kejser Dec 17 '14 at 22:13
  • @ThomasKejser: At design time I can say what types of units I need (ie length, surface area, volume, per-item) Although sub-categorization is somewhat open ended (eg: volume may be in metric or imperial units, depending on manufacturer) – tom Dec 17 '14 at 22:17
  • Is each product always sold by a single unit? Or are there products that can be sold by multiple units? It sounds like you are saying that a particular product will always be sold by a single unit. – Justin Cave Dec 18 '14 at 0:41
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.

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.