Not sure how much of help this will be for you. but I had to have an inventory database for a pub. I had similar problem with different measurements, in that at times they could sell a whole bottles and at times n number of liquid units from the barrel. it is not as complex as your problem, but it has similarities.
What I ended with, is defining a few rules:
- Every product has volume
- Every product can have a Base product.
- Every product must either multiply or divide it's base.
Now, let's say they brought a barrel of 20 liters, and they want to sell mugs of 500ml, 330ml or whatever they have for that matter.
They'd first define a single ml of beer with a volume of 1. then they can define a whole barrel of 20kml and based that on the beer while saying that the barrel is multiplying the beer. then if they want to sell mugs of 500ml, they will define a new product based on the 20k ml barrel that has a volume of 500, but this time it will divide from the beer.
We can now buy different quantities from the supplier and distribute it in different quantities to the customers.
Your problem is slightly more complex. but perhaps this will give you a general direction.
There are a few things you want to factor into your design:
1. Measurements Need a Timestamp
Make sure all of your measurements have an indication of:
- Scalar Value
- Unit of Measurement
- Date and Time the Measurement was Taken
This will allow you to work with measurements that need time-dependent conversion calculations.
2. Units of Measure Have Attributes
Each unit of measure has a few different attributes. The obvious ones are indicative, like a code and maybe a descriptive name. There are also a couple of critical other attributes to keep for each unit of measure. (i) Unit Type and (ii) Conversion Factor to the Base Unit.
The first tells you whether your unit of measure is a length, a weight, energy, power, currency, etc. etc. It should also tell you what the base unit of measure is. You should pick exactly one for each unit type. You can use things like kWh if you like, but I'd stick to the base SI units (as applicable) if I were you.
The second tells you what your unit of measure needs to be multiplied by to get it to the base. I mentioned that this is an attribute of your UOM, but in fact it needs to be in a child table. The business key of the child table that holds this base conversion factor is the combination of the UOM, its base unit type and a date/time. I would keep both an effective and an expiry date/time on the base conversion factor table. This allows you to quickly find the right rate that applies at any particular point in time. If it happens to be a rate that doesn't change, that's OK. Just use a min-collating effective date and a max-collating expiry date for the one record.
3. Trying to Table-Drive Everything Will Make You Nuts
The last piece of the puzzle is determining the calculation for moving from one kind of unit to another kind of unit. You could try to table-drive this kind of calculation but in the end the tricky ones are going to make the design so general (read complicated and slow) that it will be impractical. Instead, create a code-table of conversion calculations and use it to link one kind of Unit Type to another kind of Unit Type. Perform the actual calculations in some code somewhere. Which piece of code you use for any given conversion is what the code table tells you. How the calculation is performed is just in the code. You can have one calculation each for the various easy things, like area needs two lengths and volume needs three lengths as well as the harder ones like work needs energy and time.
When you get the details of your design figured out you should blog it and come back here to post a link!
Best Answer
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:
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.