Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!comp.vuw.ac.nz!actrix!Mary.Curtin
From: Mary.Curtin@bbs.actrix.gen.nz (Mary Curtin)
Newsgroups: comp.databases
Subject: relational theory problem
Message-ID: <1991Feb7.120950.23170@actrix.gen.nz>
Date: 7 Feb 91 12:09:50 GMT
Sender: Mary.Curtin@actrix.gen.nz (Mary Curtin)
Organization: Actrix Information Exchange, Wellington, New Zealand
Lines: 68

I am working with a table which has the following fields:

   health_indicator   -  a text description of a number
   numerator          -  text description of one of the data items 
                              used to calculate the number
   denominator        -  text desc'n of the other data item
   num'r_definition   -  long text field defining the numerator
   denm'r_definition  -  long text field defining the denominator

An example record would be:

    health_indicator   Asthma deaths per thousand children
    numerator          Asthma deaths of children
    denominator        Children
    num'r_definition   Deaths coded ICD9-CM 493.00 - 493.99, aged 0-14 yrs
    denm'r_definition  Population aged 0-14 years

On the surface, this would seem to normalise as:

   T1(health_indicator, numerator, denominator)
   T2(numerator, num'r_definition)
   T3(denominator, denm'r_definition)

but there is a problem.

The numerators and denominators are all instances of (descriptions of) 
DATA ITEMS - so something which is a numerator in one record may be a
denominar in another.  For example, there may be a record like:

   health_indicator     Percent of population under 15 years
   numerator            Children
   denominator          Population
   num'r_definition     Population ages 0-14 years
   denm'r_definition    Total population

(Notice the different roles played by 'children' in the two example records.)

This means that instead of T2 and T3 in the first suggestion, I would
simply need one table of data items and their definitions.  But this
is difficult to implement, due to the presence of _two_ relationships
from T1 to the data items table.

Now I really want to normalise this table - the redundancy involved in
having multiple copies of definitions has caused me *heaps* of problems.

Has anyone got any suggestion how?

I thought of having multi-table forms, but the software I'm using
won't let me have two links from one table to another.  I also thought
of having two identical 'definitions' tables and linking the numerator
to one and the denominator to the other, but this causes problems with
updates ... how do you make simultaneous changes to numerators and
denominators (it is really important that I can look at a record and
fix the definition of the numerator and the denominator at the same time.)

We are currently working in Paradox 3 - mainly because it is simple
enough to let the majority of users, who have minimal computer skills,
do the sorts of things they want to do easily.  If someone could
suggest a solution in another package running under MS-DOS, we might be
able to think about changing ...


{BTW - are the any other New Zealand users of Paradox out there?}
-- 
\   \/   /  |  
 \/\/\/\/   |  mary.curtin@bbs.actrix.gen.nz
  
  Epidemiologist = a doctor, broken down by age and sex?