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
