I hit the following problem in various permutations all the time in data modelling.
Here is specific example. All of my solutions tend to result in messy code at
the front end. Can someone guide me to a "proper" way to model data like this.
A base table consists of measurements that have been made on a hunk of
rock. Ignoring the issues of sample table, analyical method etc. you have an
table that might look like this:
*ResultId
SampleId
Result
Analyte
The SampleID points to a sample table (so can have many results from one sample)
*SampleID
DepthTo
DepthFrom
...
SiteID
SiteID points to an x,y location which will typically be a well or a rock outcrop.
Now if all samples came from wells, then the world would be rosy. The outcrop
introduces a problem - this is the property of a sample which is the rock formation
name the rock came from.
A very typical query would be "give the mean of all results for analyte x where
formation is Ugly Sandstone."
Where does the system get the formation from? If SiteID is a pointer to a well,
then there is a table FormationLog:
*SiteId
*Formation
DepthFrom
DepthTo
that logs this formation property. You can look at DepthTo,DepthFrom in Sample
and return the formation the rock came from. (Ignoring possibility that the
depthto/from
spans a formation boundary). Ie you can code a simple function
GetFormation(Depthto,DepthFrom) to
return Formation from the other table. If the SiteID is an outcrop, the formation
must be explicit entered into a table somewhere.
My solution to such dilemmas has been to create a Formation table
*SampleID
*formation
and load it only for outcrops. Then GetFormation code can use conditional
logic to either read the value from Formation Table, or look it up from the
FormationLog
depending on nature of SiteID.
Easy enough to create readonly views. Data documentation and rules gets somewhat
messy though. Is there a better way to structure these cases of conditional data
structure in an alternative model?
----------------------------------------------------------
Phil Scadden, Institute of Geological and Nuclear Sciences
41 Bell Rd South, PO Box 30368, Lower Hutt, New Zealand
Ph +64 4 5704821, fax +64 4 5704603
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
