Best way to store units in database

Question

I have inherited a large (SQLServer) database with hundreds of columns that represent amounts of one thing or another. The units for these values (e.g. "gallons", "inches", etc) are stored in the MS_Description field of Extended Properties. I am wondering if there is a better way to store this information. I suppose it is fine for documentation purposes, but it would be difficult to make robust unit-conversion calculations based on this data. At this point I am not prepared to make an invasive change, but if I get the chance to do so, what is the recommended Best Practice in this regard? Options, off the top of my head, might include:

• Change column name to included units (e.g., "TotalVolumeInGallons". This would make the information a little more readily available, but it still seems weak to me.)
• Add a separate "Units" column to correspond to every "Amount" column (this column could be nvarchar OR it could be a foreign key to a separate Units table which might make it easier to calculate unit conversions. On the other hand, adding so many columns could fairly double the size of my database -- with terribly redundant data.)
• Create a new field in Extended Properties dedicated specifically for units. (Unfortunately, I don't think this can be a foreign key to a Units table.)
• Is there another idea that I'm overlooking?

UPDATE: After reading @Todd Everett's answer, a possible solution occurred to me, so I'm going to go ahead and answer my own question. (See below)

Answer 1

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.

Answer 2

All work.

Note that in the second case, you cannot add apples and oranges, and so the data is exceptionally easy to be subject to misinterpretation.

Also note that conversions cannot be very safe and are susceptible to rounding error, overflows, etc.

In addition, there are physical issues like the specific gravity and temperature. Converting 20 gallons of water to pounds would require you to know the density of water. But water's density changes with temperature, so you may need to either know the density contemporaneous to the measurement or the temperature similarly and use a volume correction factor.

In the case of the Extended properties, that's only good for documentation - a good column name is better for documentation. The problem with the column implied as being in a fixed unit by name is that you end up putting yourself in a corner when you change measurement units - new client wants oil in barrels and not gallons - and that would be fine since their data is in its own database, but the column name is now misleading.

Another option is to store canonical versions in fixed units (i.e. always kilograms and meters) in addition to the varying original measurements. Aggregate operations on the fixed units should be fine (except you wouldn't add temperatures, for instance), but you don't lose the original measurement.

Answer 3

A simple solution that has worked well for me in the past is to store all your data in the 'base' units. For example, your base unit for lengths may be millimetres and your base unit for weights may be kilograms. This solution may result in the need to convert some of your existing data into the base unit, if it isn't already.

Once you have all the data in the standard base units, there is no need to store the unit in the database itself, since it is now a system wide assumption. The displayed units required for each unit type (e.g. whether to display mm,inches,cm,m for length) becomes an application/client domain issue, which can be saved to local storage.

The unit conversion tables for converting between the various supported units can be hardcoded within your application, since new units of measure change extremely rarely.

N.B. a related solution to another issue is that when storing timestamps in a database to always store them in the 'base' unit - UTC.

Another related Q&A on the topic ...

• https://stackoverflow.com/questions/12977021/best-practice-for-storing-weights-in-a-sql-database

• This contains some good information on why using a floating point column type is the best way to go when storing real world measurements.

Answer 4

Since any unit can be converted to another unit of the same type With the formula:

y = ((x + xOffset) * multiplicand / denominator) + yOffset

I would create a table which contains the unit types plus these 4 values.

From Unit     To Unit      Unit Type    From Offset    Multiplicand    Denominator    To Offset
'milligrams'  'grams'      'mass'       0              1               1000           0
'grams'      'kilograms'   'mass'       0              1               1000           0
'grams'      'ounces'      'mass'       0              100000          2835           0
'ounces'     'pound'       'mass'       0              1               16             0

After having added all the measurements you are likely to convert to and from are on either side of the list, run a Query where you insert the inverse operation by simply negating the offsets and swapping multiplicand and denominator and the To Unit and From Unit.

To add Conversion between all types, a cross join With some filters can insert the remainding Conversions.

Answer 5

After reading @Todd Everett's answer, a solution occurred to me, so I'm going to go ahead and answer my own question. What I think I'm going to do is to create a separate ColumnUnits table, with four columns: Schema, Table, Column, UnitsID (where UnitsID is FK to a separate UnitsOfMeasure table), thus mapping any given column to its associated Unit of Measure. Obviously the biggest downside of this idea is that developers would have to remember to edit this table whenever they rename a column or table [perhaps use a DDL trigger?], otherwise the system will break. But assuming such renamings are rare, and the dev-shop small (just one person, in my case), this architecture should be workable. The advantage is that no invasive changes have to be made to the current DB, and I only have to store the value once for every column, rather than once per row as my second option in my original post would require.