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)

  • Best practice is to have a single measurement system used universally and consistently throughout the application. SI would be the system of choice. Values in other systems will be converted during loading or in the presentation layer, where each user can choose her preferred set. Commented Apr 23, 2015 at 5:05

5 Answers 5


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.

  • Thanks for some very interesting food for thought -- I learned a lot. However, I don't think EAV is the appropriate model in my case (if I understand your suggestion correctly) because, although we do have 100s of columns, they are by no means sparse. However, this DID spark a related idea (see UPDATE in my original post).
    – kmote
    Commented May 30, 2012 at 15:31
  • Your idea sounds pretty good to me - I can't think off hand of any issues with it other than what you already pointed out. But if columns can be renamed/changed that would be a problem in any design. This is when collaboration is fun - an idea surfaces that neither of us thought of to begin with! Commented May 30, 2012 at 22:16

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.

  • 1
    The potential "misinterpretation" you mention is exactly one of the concerns I have about the current architecture of this database -- and something I'm trying to figure a way to reduce.
    – kmote
    Commented May 29, 2012 at 21:30
  • 1
    great point about the potential drawback of the column-name solution.
    – kmote
    Commented May 29, 2012 at 21:33
  • 1
    @kmote It's not a simple problem - we have reports where the individual transactions may have varying original measurement units, but there is also a total - which is a total after conversion to a user-selected unit.
    – Cade Roux
    Commented May 29, 2012 at 21:36

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 ...


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.


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.

  • 1
    interesting puzzle... and interesting idea you have. your idea would make it easier to query, but doesn't seem to achieve much. you've just moved the reference data to a different place. what bothers me most about this design Commented Apr 23, 2015 at 9:18
  • ...is that if an item has more attributes you still need to add more columns. for that reason i like @todd everett's suggestion of an eav design. Commented Apr 23, 2015 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.