Re: Setting a FK to look at only selected rows in the 'look-up' table...

--------------------------------

-----Original Message-----
From: Jeff Waugh [mailto:jwaugh@griddlecat.com]
Sent: Friday, December 28, 2007 6:24 PM
To: Greg Cocks
Subject: Re: [NOVICE] Setting a FK to look at only selected rows in the
'look-up' table...

"Greg Cocks said"
>
> Hello,
>
> In my data table <d_borehole> I have a field 'depth_unit' that is used
to r=
> ecord the measurement unit of various numeric depths down a borehole.
>
>
> I have a 'look-up' table <r_unit> that contains three fields:
>
>  - 'unit_id' - PK, the abbreviation for the unit - example: "m"
>
>  - 'description', fuller description of the unit - example: "metres"
>
>  - 'unit_type', the nature (sic) of the unit - example: "length"
>
>
> The 'unit_type' field has a variety of values - "length", "ratio",
"volume"=
> , etc
>
>
> I would like to restrict the foreign key for d_borehole.depth_unit to
be on=
> ly those values in r_unit.unit_id where r_unit.unit_length =3D
'length' (i.=
> e., so that only applicable 'length' units can be utilized for the
depths (=
> feet, metres, inches, etc))
>
>
|
|Instead of storing the depth_unit in d_borehole, store depth_unit_id.
|
|Then it would be something like:
|
|alter table d_borehole add constraint unit_type_fk
|(depth_unit_id) references r_unit (unit_id);
|
|unit_id will need to be the primary key (or at least unique) in r_unit.
|
|That is pretty much the standard way to use lookup tables. Don't store
the
|lookup value anywhere except the lookup table. Anywhere else you want
|that lookup value, store the primary key from the lookup table, then
|when you need the text 'look it up'.

|HTH.


Jeff,

Thanks for the reply...

I think I am missing something - sorry...

It seems that is the way I have it now - i.e., 'depth_unit' == 'unit_id'
(sic), where the later is the PK in the look up table <r_unit>

That is, the names are just different - maybe they should not be?

If I normalized (sic?) it by using, say, an integer for the unit_id I
both places it seems to me that there would still be the same issue of
how to parse the values 'available' through the FK from <r_unit> to be
*only* be those where the unit_type = 'length' in <r_unit> i.e.:

SELECT r_unit.unit_id FROM r_unit WHERE r_unit.unit_type)='length';

Note that another data table might, say, only be "allowed" to use
unit_type = 'velocity'

Aside - I like using the abbreviated and yet unique values for the unit
identifier - ft, m, ppm, ppb, etc - as the 'raw' data table seems that
much more readable - and I don't appear to be causing any issues - do I
need to be corrected on this approach?  :-)

Cheers:
GREG...