Re: Units of Measurement in the Database Model

I choose the 4th one.
Only need one field in the measurement record,Database size issue can be ignored:(

johnrlewis_at_hotmail.com (John R. Lewis) wrote in message news:<4323c1b8.0204111001.4cd074e2_at_posting.google.com>...
> 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 Fri Apr 12 2002 - 02:17:39 CEST