Best way to store units in database

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)

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.

StackExchange Database Administrators Q#18539, answer score: 14