Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

4 Answers 4

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.

share|improve this answer
    
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 May 30 '12 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! –  Todd Everett May 30 '12 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.

share|improve this answer
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 May 29 '12 at 21:30
1  
great point about the potential drawback of the column-name solution. –  kmote May 29 '12 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 May 29 '12 at 21:36
up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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