Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sun-barr!lll-winken!elroy.jpl.nasa.gov!sdd.hp.com!uakari.primate.wisc.edu!unmvax!ariel.unm.edu!nmsu!opus!agonzale
From: agonzale@nmsu.edu (Agustin Gonzalez-Tuchmann)
Newsgroups: comp.databases
Subject: Re: relational theory problem
Message-ID: <585@opus.NMSU.Edu>
Date: 8 Feb 91 17:57:52 GMT
References: <1991Feb7.120950.23170@actrix.gen.nz>
Sender: news@NMSU.Edu
Organization: NMSU Computer Science
Lines: 110
In-reply-to: Mary.Curtin@bbs.actrix.gen.nz's message of 7 Feb 91 12:09:50 GMT

In article <1991Feb7.120950.23170@actrix.gen.nz> Mary.Curtin@bbs.actrix.gen.nz (Mary Curtin) writes:


   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?


It is difficult to follow this without the explicit functional
dependencies among the different attributes. But I'll give it
a try.

You can have the tables:

T1: (code#, descripcion)
	In this table you hold all the descriptions possible
        for numerators and denominators. ie possible tuples
        are: (0001,Children), (0002,Population).

T2: (health_indicator,numerator,denominator).
	Here the key is numerator+denominator --> health_indicator.
        Numerator and Denominator are code#'s from T1.

Note: I don't seee the difficult of implementing the fact
that this table has two foreign keys. Does your dbms allow
only one foreign key for each table?


T3: (code#,Numerator_description)
	This table holds descriptions for numerators

T4: (code#,Denominator_description)
	Same as before.

I think these tables are normalized. It might not be what you want,
since it is difficult to tell the functional dependencies with
the information you provided.

I hope this helps.

--
Agustin Gonzalez-Tuchmann                    dbase-l list owner.
New Mexico State University                  Office: SH-165
Computer Science Department                  Phone: (505) 646-6243
Las Cruces, N.M. 88003-0001                  e-mail: agonzale@nmsu.edu