There is no 'proper' way for a non-trivial example. I must confess that one
of my aims when modelling is to reduce the amount of conditional code. This
is always hard when you get sub-types and states.
However, just to clarify my understanding of the problem domain, could you
indicate which statements are false.
A site can be a well or have an outcrop.
A site cannot be both a well and an outcrop.
Any site can have many samples
A well-site can have many samples
An outcrop-site can have many samples
There are several types of formations
Each specific formation is of a single type.
A specific formation can be seen at different heights on different samples.
A specific formation is only at one site
A core has many vertical sections (where a core is a single drilling
instance across multiple samples and formations)
A sample is a type of section
A formationLog[:-(] is a type of section
A site can have only one core
A site can have many cores
Each sample has a single formation type
Each sample can have many test results
A well is a type of core.
However, returning to a 'proper' way of doing things.
Rational Unified Process (and many other methodologies) recommend early on
in the piece that you capture a common vocabulary. As soon as you are making
up terms like FormationLog then you need to go back to the domain experts
and ask what this thing is. If they haven't got a word for it and it is not
an intersection entity then you normally need to review the analysis model.
Normally they have 3 words for it and you have to arbitrate.
PS. Aren't other people's design problems more interesting than your own!
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Phil Scadden
Sent: Tuesday, January 16, 2001 2:44 PM
To: Multiple recipients of list database
Subject: [DUG-DB]: Data modelling problem
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
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
