On Dec 28, 2007 7:01 PM, Greg Cocks <
gcocks@stoller.com> wrote:
Hello,
In my data table <d_borehole> I have a field 'depth_unit' that is used to record 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 only those values in r_unit.unit_id where r_unit.unit_length = 'length' ( i.e., so that only applicable 'length' units can be utilized for the depths (feet, metres, inches, etc))
This cannot be done with foreign keys I don't think. I would store the unit_id (an integer, not an abbreviation as above) as you have been doing and then have your application code do the lookups for the appropriate fields. Maintain the foreign key to be sure that your unit is in the "unit" table, but your application would be responsible for making sure that length units are used for lengths, width units for width, etc. You might want to split out the unit_type into a separate lookup table to allow you to add/modify unit_types easily and quickly.
Sean