Re: Units of Measurement in the Database Model
Date: Tue, 16 Apr 2002 23:18:29 GMT
Message-ID: <9j2v8.1115$2E.31590_at_news2.ulv.nextra.no>
"David J. Aronson" <dja2001_at_att.net> wrote in message
news:3CBC99A9.E63DAA2D_at_att.net...
> 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 upwhat the UoM is for a given field in a given record of
> a given table". To quote John's original message:
>> > When the application is configured for a particular customer, UOM
> > 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".
> > choices would be recorded in this table.
Ok, I get your point. (I didn't read the original message.)
> (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:
>> > This field would be a foreign key to a lookup table containing all
> > 4) UOM Foreign Key per measurement
> >
> > This solution would add one additional field per measurement recorded.
> > 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.
Right on, although I do dislike the use of the term "foreign key" when discussing lookup tables.
> 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.
Rest assured, this never would have entered my mind. ;-)
Daniel Received on Wed Apr 17 2002 - 01:18:29 CEST