Re: Units of Measurement in the Database Model

Daniel Dudley wrote:
>
> "David J. Aronson" <dja2001_at_att.net> wrote in message
> news:3CBC3F91.1AF68152_at_att.net...
> > 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".
>
> No, David, in your message (see above) #3 is the Lookup
> Table case.

Right, but he was using the term "Lookup Table" not to mean "table where you look up what a given UoM code means", but "table where you look up what the UoM is for a given field in a given record of a given table". To quote John's original message:

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

(Oops, I just realized, one of my criticisms of it was not as valid as I thought, since this seems to be saying that the column has one and only one UoM, not varying by record. If that's the case, though, then there is no longer the flexibility to have drastically different things measured in the same table, such as a state's average temperature, a parking lot's capacity, and an insect's length.) By contrast, #4 is:

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

Now as for what you (Daniel) were saying, let's forget about the numbers for a moment and just refer to them by concept.

If, as I thought, you were thinking of the "foreign key into UoM registry" case, then I agree with all that you said. It's a wonderfully flexible, simple, and efficient way to go. I "voted" for it originally.

If you really meant the "UoMs are *only* recorded in a separate table, where the measurement record is referenced by table and field" case, then I think we've got some debating to do. Not only don't I see how this way is likely to lead to a smaller database (whether the UoMs vary by record or not), but it seems overly complicated and inefficient, to the point where I really don't see any advantage to it.

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