Re: Units of Measurement in the Database Model
Date: Tue, 16 Apr 2002 19:07:16 GMT
Message-ID: <ED_u8.858$ph2.21051_at_news4.ulv.nextra.no>
"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.
Because lookup tables are mainly used as an integrity check for data being entered in other tables, a relation (link) into a lookup table is not strictly required. Indeed, a permanent link would be inefficient because access to the lookup table is not required when browsing, reading from or writing to other tables. Efficiency is best obtained by reading the whole lookup table into an array or list, which is then searched when verifying data (UoM, in this case) entered into a related column in other tables -- a typical "after trigger" function in a DBMS. Alternatively, the user may select from a list (of valid UoMs, in this case), which is presented to the user when entering a related column of another table during data entry -- a typical "before trigger" function in a DBMS.
As a point of order, we can note that lookup tables are usually quite small in size and, because they are rarely modified, they are read in just once in a working session.
This arrangement doesn't lead to a smaller database (as noted by you, David). It does, however, ensure the integrity of the database. And it is efficient.
[snipped]
Daniel Received on Tue Apr 16 2002 - 21:07:16 CEST