Re: Units of Measurement in the Database Model

In message <4323c1b8.0204111001.4cd074e2_at_posting.google.com>, John R. Lewis <johnrlewis_at_hotmail.com> writes

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

True, but that's unlikely to be a significant problem. Typically quantities that have units of measurement are analogue and there is always some uncertainty in measuring them.

The situation you need to be aware of is when you sum converted and unconverted figures. You should not expect the sum of the converted figures to equal the number you get when you sum the unconverted figures then convert the result.

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

This seems a completely pointless elaboration of the model. It simply records metadata within the tables instead of in the field names.

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

Another con is that you will still get rounding errors. If some measurements are in feet and some are in metres you need to convert them to a common unit to make comparisons. You may get a different length if you sum the values in feet and compare it to the sum of the values in metres.

You will just have to live with the fact that the universe is imperfect.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti