Mary.Curtin@bbs.actrix.gen.nz (Mary Curtin) (02/07/91)
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?agonzale@nmsu.edu (Agustin Gonzalez-Tuchmann) (02/09/91)
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.edudberg@informix.com (David I. Berg) (02/10/91)
> I am working with a table which has the following fields: > .... > An example record would be: > .... > On the surface, this would seem to normalise as: > > T1(health_indicator, numerator, denominator) > T2(numerator, numr_definition) > T3(denominator, denmr_definition) > .... > 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: > .... > (Notice the different roles played by children in the two example records.) > .... > 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? Begin by building relations T1 and T2 like this: T1(health_indicator, numr_code, numr_value, denr_code, denr_value) T2(numr_denr_code, numr_denr_definition) The key to T1 is health_indicator. T1 has foriegn keys numr_code and denr_code. The key to T2 is numr_denr_code. There is a 1 to 2 relationship from T1 to T2, and a 1 to many relationship from T2 to T1. This is because T1 contains a multi-valued dependency; namely, the values for numr_code and denr_code come from the same domain of values. However the application works. Example records would be: T1: health_indicator Asthma deaths per thousand children numerator_code Asthma deaths of children numerator_value m denominator_code Children denomiator_value n health_indicator Percent of population under 15 years numerator_code Children numerator_value m denominator_code Population denomiator_value n T2: numr_denr_code Asthma deaths of children numr_denr_defn Deaths coded ICD9-CM 493.00 - 493.99, aged 0-14 yrs numr_denr_code Children numr_denr_defn Population aged 0-14 years numr_denr_code Population numr_denr_defn Total population To eliminate the multi-valued dependency in T1, we must normalize T1 into fourth normal form (4NF). This yeilds the relation: T1(health_indicator, numr_denr_indr, numr_denr_code, numr_denr_value) The key to T1 is (health_indicator, numr_demr_indr). It has the foriegn key numr_demr_code. Now, our example records in T1 are: health_indicator Asthma deaths per thousand children numr_demr_indr N numr_denr_code Asthma deaths of children numr_demr_value m health_indicator Asthma deaths per thousand children numr_demr_indr D numr_denr_code Children numr_demr_value n health_indicator Percent of population under 15 years numr_demr_indr N numr_denr_code Children numr_demr_value m health_indicator Percent of population under 15 years numr_demr_indr D numr_denr_code Population numr_demr_value n The relations T1 and T2 are now fully normalized to 4NF. ___ ___ dberg@cougar.informix.com / ) __ . __/ /_ ) _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__> (-' -/~ (_- 5299 DTC Blvd #740; Englewood CO 80111 {uunet|pyramid}!infmx!dberg The opinions expressed herein are mine alone.