Custom unit of measurement conversion problem

Question

Let's say I have something that represents an item, let's say Beer. I store it in a table "Items". I also have a table with units of measurements. Like box, as beers can be sold in boxes.

Now the problem is, the box can have different amount of beers, there can be a box of lets say 20 beers and there can be a box of 10 beers. So there are a couple of possible conversion factors depending on the item.

Also the item can be sold in different unit, like a box, pack and each box and pack can have different amounts of items inside.

How should I design my database to handle this problem?

Answer 1

Your problem is not a unit of measurement problem. It's a stock keeping unit (SKU) problem. You can't convert, nor for any practical purposes would you ever want to convert from 8 packs of cans of beer A to 12 packs of bottles of beer B.

Your product ("Beer A") is a generic product header. You don't sell "Beer A". What you actually sell are the boxes of cans (or bottles) - of a particular size - of Beer A. This box is what gets a record in your product/item table. You may want to have a product header table that just lists "Beer A" without any actual quantity related to it. That's up to you and depends on your system requirements. What you really need to have though is a SKU table where the actual salable things are recorded.

Now if you want to be able to get some kind of apples to apples comparison between SKUs of similar products, you can track an actual, objective unit of measure for each SKU. For example, if you're dealing with beer, then a liquid measure is appropriate. Say you have 6 x 355ml cans of Beer A in your six pack SKU. Then that SKU has 2,130ml of beer in it. You could store that along with the other useful information that it's a six pack of 355ml cans.

If you want to have this apples to apples comparison, then you want to have a unit of measure conversion table that has columns such as:

...
  measure_type char(1) -- `D`ry or `L`iquid (or others as applicable)
, from_unit_of_measure varchar(10)
, to_unit_of_measure   varchar(10)
, conversion_factor    decimal(16,8)
...

If you have something based on these columns then you have to have all the combinations of from / to units of measure in both directions in order to look up a conversion factor. You could also, alternatively, only have from non-default units of measure to the standard unit of measure for each measure_type. This would mean less data in the table, but you'd have to have conditional logic to go from and / or to non-standard units of measure. In the worst case scenario, you might have to convert from units in A (non-standard) to B (standard) and again from B (standard) to C (non-standard). Which way you go depends on your relative tolerance for more data to maintain or more code complexity.