Units of Measurement in the Database Model

I am designing the Data Model for a large enterprise application, and have run into a problem. I solicit your feedback.

The Problem:

How should the database be organized in regards to measurements recorded, and the units of measurement (UOM) used to record them?

When recording large numbers of measurements, it is important to know what is being measured. A number is meaningless without its UOM.

As a side issue, in this design, many important tables contain a foreign key to a "Signature" table, where a digital signature for that record is stored. If ever the record is changed, the signature becomes invalid.

The Solutions:

1. Don't Record UOM in Data Model

One solution is to keep the data model completely unaware of the UOM being used for a particular measurement. This solution would require the Application and/or Presentation logic be aware of the UOM.

PRO
* Simple Data Model (Narrow Tables)

• Smaller Database size CON
• All code that accesses the data must be configured identically, or the data is meaningless.
• If ever the UOM needs to change for a particular measurement, either the old data becomes meaningless, or the old data gets changed, thus invalidating the digital signatures associated with the affected records. 2) Hardcode the UOM in the Data Model

Another solution is to hard code the UOM in the Data Model, and never allow for changes. For example, instead of having a field named "OAT" for outside air temperature, have a field named "OATDegC" to denote the UOM being used.

PRO
* There is never any confusion in regards to the UOM being used.

• Smaller Database size.
• Most convenient data model for which to write application and presentation logic. CON
• For the customer who wishes to record a particular measurement using different UOM than the hard coded default, it is possible for rounding errors to occur. 3) UOM Lookup Table in the Data Model

This solution would have a table named "UnitsOfMeasurement" that would consist of the fields: "TableName", "FieldName", "UnitsOfMeasure". When the application is configured for a particular customer, UOM choices would be recorded in this table. PRO
* No UOM confusion

• Smaller Database size CON
• Slightly more complex Data Model
• If ever the UOM needs to change for a particular measurement, either the old data becomes meaningless, or the old data gets changed, thus invalidating the digital signatures associated with the affected records. 4) UOM Foreign Key per measurement

This solution would add one additional field per measurement recorded. This field would be a foreign key to a lookup table containing all possible UOM.

PRO
* No UOM confusion

• The UOM can vary from record to record. CON
• Database size may become an issue
• More complex data model

Thank you for reading this posting. I hope someone out there can help. Received on Thu Apr 11 2002 - 13:01:52 CDT