Re: [NOVICE] 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

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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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

---------------------------(end of broadcast)---------------------------