From: | "Greg Cocks" <gcocks(at)stoller(dot)com> |
---|---|
To: | "PostgreSQL List - Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Seeking advice on how to build a pseudo-FK (?) relationship |
Date: | 2008-02-13 04:24:37 |
Message-ID: | 66F6CF82BF58CE4DB4285BE816B297E840ED8E@tribble.SMStoller.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I am seeking advice on how to build a pseudo-FK (?) relationship.
I have a table to carry some time series data derived from a variety of
transducers and other instrumentation - with data fields like station,
D&T, parameter, supplied value, supplied value unit, and a few other
things.
For example, for weather data some rows might contain:
WS1, 12/31/07 16:15:00, air_temp, 22.63, deg_F, ...
WS2, 12/31/07 22:30:00, air_temp, 6.78, deg_C, ...
WS1, 12/31/07 16:15:00, BP, 1046.71, mbar, ...
The unit of the value is linked to a unit lookup table via a FK, as is
the parameter to separate lookup table, the station to a location data
table, etc.
I have added a couple of fields called value_uniform and unit_uniform so
that when comparing, say temperatures, I am comparing apples & apples,
etc (but still want to keep the data 'as supplied' in the same row, for
QA purposes, etc) - and I am starting to set up some triggers, etc to
populate these fields.
I wanted to add a 'fail-safe' in here so that the unit_uniform is just
that... and so that (eventually) the triggers know how to convert the
data, via a conversion lookup table.
So in the parameter lookup table, I have added a field called
parameter_uniform_unit. The parameter is a PK, but the
parameter_uniform_unit is not necessarily unique (for instance, many
parameters might want to be 'normalised' to degrees F as a unit.)
*How do I get the instrumentation data table unit_uniform field to only
have a state of NULL |or| a value corresponding to the appropriate
parameter_uniform_unit unit for that parameter?* (i.e., in the parameter
lookup table.)
You can't use sub-queries in check constraints, so I can't do a
[unit_uniform IN (SELECT...)] type deal...
I can't add a FK to the instrumentation data table with two columns
(parameter and unit) referring back to the parameter lookup table,
because of the parameter_uniform_unit's non-uniqueness (sic.)
*Do I need to think about some design changes here?*
Any suggestions gratefully received...
TIA!
----------
Regards,
GREG COCKS
GIS Analyst V
Gcocks|at|stoller.com
S. M. Stoller Corp
105 Technology Drive, Suite 190
Broomfield, CO 80021
www.stoller.com
303-546-4300
303-443-1408 fax
303-546-4422 direct
303-828-7576 cell
From | Date | Subject | |
---|---|---|---|
Next Message | johnf | 2008-02-13 07:20:46 | trigger that needs a PK |
Previous Message | Mark S | 2008-02-12 23:07:13 | Re: Casting integer to boolean |