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)