Re: Units of Measurement in the Database Model

Daniel Dudley wrote:
>
> "David J. Aronson" wrote in message
> news:3CBB21D5.DD95CDAD_at_att.net...
> > "John R. Lewis" wrote:
> [snipped]
> > > 3) UOM Lookup Table in the Data Model
> > ...
> > > PRO
> > ...
> > > * Smaller Database size
> >
> > I don't see how. The overhead of recording each record's
> > UOM along with a reference to that record, must be larger
> > than not recording it.
>
> Note that he stated Lookup Table, which implies that only
> units registered in that table may be entered in other
> UOM columns in othert tables.

I think you're thinking of the "foreign key into UoM registry" solution; that was #4. #3 was "separate table stating what the UoM is for each field of each measurement table".

There are two things I can think of that he might have meant, in saying that this leads to a smaller db:

• The space required to store each record's UoM in a separate record (including table, field, and UoM IDs), might be smaller than spelling out the UoM name in full in each measurement record, more than enough to offset the overhead of the new table. This is quite likely if the original record spelled out "square kilometers", but not if you abbreviated it to "SK" or some such. Furthermore, the UoM would still have to be either spelled out (thus taking even more space than leaving it spelled out in the measurement record), or used as a foreign key (thus taking more space than #4), if anything useful is to be done with it.
• Extracting the UoM allows the quantity to be expressed as a number, rather than a string for the quantity and UoM combined (e.g., "4 feet"), which will generally allow it to be smaller.

John, was it one of those, the combination, or something else?

-- 
David J. Aronson, Software Engineer FOR HIRE IN PHILADELPHIA AREA
Resume, and other details, online at: http://dja2001.home.att.net