patternsqlMinor
What's a good relational structure for units and complex unit conversions?
Viewed 0 times
unitsconversionswhatrelationalforstructuregoodandunitcomplex
Problem
My company is in the Energy industry and I need to come up with a good way to represent the conversion of units of measurement. I've done some searching and have yet to find a good article covering this at the depth I need. Most info published about unit conversions assume that given Unit 1 there is a known (hard-coded) conversion rate to get to Unit 2 and it's simple math (this being the most complex example I've found which still doesn't help). However, this isn't always true in the real world and certainly isn't true for what we must handle. (Sorry for the long write-up - I'm trying to provide as much info as possible!)
Tricky example 1:
Some conversions vary with time, such as converting $5 to Euros or vice versa. This sounds like it has nothing to do with the energy but it really does in the energy commodities market (think stock market).
Tricky example 2:
(Oversimplified) Some natural gas burns hotter than others. In addition, natural gas can be measured/stored either based on the Energy in the gas (such as Therms) OR based on the Volume of said gas (such as MCF which is 1000 Cubic Feet), and there are other possibilities too (such as Ton for Mass). An analogy of gasoline is that 1 gallon of 87-octane Unleaded provides less energy than 1 gallon of 93-octane Unleaded.
Tricky example 3:
In addition to having these units of measurement, we also often have to deal with rates, such as $ per Therm or € per MCF. So we need some way to work with these rates and how they relate to the base units so if we need to convert from $ per Therm to € per MCF, we can and it utilizes the same published rates as converting from Therm to MCF.
Tricky example 4:
Previously, I've used the term Energy very loosely and possibly sometimes incorrectly. At this point and from now on, that's changing. So the last curveball is that we deal with both Energy as well as Power. With electricity, this means kWH versus kW (a fairly good explanation despite it being Yahoo Answers). A data analo
Tricky example 1:
Some conversions vary with time, such as converting $5 to Euros or vice versa. This sounds like it has nothing to do with the energy but it really does in the energy commodities market (think stock market).
Tricky example 2:
(Oversimplified) Some natural gas burns hotter than others. In addition, natural gas can be measured/stored either based on the Energy in the gas (such as Therms) OR based on the Volume of said gas (such as MCF which is 1000 Cubic Feet), and there are other possibilities too (such as Ton for Mass). An analogy of gasoline is that 1 gallon of 87-octane Unleaded provides less energy than 1 gallon of 93-octane Unleaded.
Tricky example 3:
In addition to having these units of measurement, we also often have to deal with rates, such as $ per Therm or € per MCF. So we need some way to work with these rates and how they relate to the base units so if we need to convert from $ per Therm to € per MCF, we can and it utilizes the same published rates as converting from Therm to MCF.
Tricky example 4:
Previously, I've used the term Energy very loosely and possibly sometimes incorrectly. At this point and from now on, that's changing. So the last curveball is that we deal with both Energy as well as Power. With electricity, this means kWH versus kW (a fairly good explanation despite it being Yahoo Answers). A data analo
Solution
There are a few things you want to factor into your design:
Make sure all of your measurements have an indication of:
This will allow you to work with measurements that need time-dependent conversion calculations.
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.
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!
- 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.
- 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.
- 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!
Context
StackExchange Database Administrators Q#12590, answer score: 5
Revisions (0)
No revisions yet.