Path: utzoo!attcan!uunet!aplcen!haven!decuac!shlump.nac.dec.com!arkham.enet.dec.com!nmeser!may
From: may@28182.dec.com (Patrick May)
Newsgroups: comp.databases
Subject: Re: Tricky database definition problem
Message-ID: <54@arkham.enet.dec.com>
Date: 12 Jan 90 16:21:33 GMT
Sender: news@arkham.enet.dec.com
Organization: Digital Equipment Corporation
Lines: 58


>I have a database consisting of observations.  Each observation consists of a
><DataType, DataValue> pair (the observation might be water height or machine
>temperature or any number of other things).  Associated with each pair is a
>list of <LocationType, LocationValue> pairs that help identify where the
>observation occurred.  This can be defined simply (and rather redundantly) in
>one table like (with some example data):
> 
>	Observation
>	DataType	DataValue	LocType		LocValue
>	WaterHeight	10		Shore		South
>	    "		 "		Time		12:30
>	    "		 "		Observer	Bill
>	WaterHeight	15		Time		12:30
>	    "		 "		Observer	Fred
>	WaterTemp	60		Time		1:30
>	    "		 "		Observer	Tom
>	WaterTemp	50		Time		2:30
>	    "		 "		Observer	Gerry

>With a table like this (or more normalized), can a query be written like to
>find <DataType, DataValue> that are constrained by some <LocType, LocValue>
>pairs.

Maybe I'm misunderstanding your explanation, but from the example I see no way
to relate a time to a measurement.  Your apparent goal is a database of data
collection records composed of the following:

          Observer
          Time
          Location 
          Observation Type
          Observed Value

Each Observer may be related to many Time-Location pairs and may make many
observations at that Time-Location.  I would therefore model this system using
the following tables:

     Time.Location
          Observer_ID
          Time
          Location_ID

     Observation_Data
          <foreign key from Time.Location>
          Observation_Type
          Observation_Value

     Validation Tables for Observer, Location, and Observation_Type.

There is a one-to-many relationship between Time.Location and Observation_Data.
The primary key fields in Time.Location (all three listed) may be supplemented
with a system generated surrogate key to minimize the number of key fields in
Observation_Data (although I try to avoid such constructs).

Hope this helps,

Patrick