How to normalise?

#1

We have trial where young children are tested for their levels of
cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother and/or
father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia their
parents are also tested for the same condition. In the case that the
child's biological parents aren't known, we won't follow up with these
tests.

Here's the puzzle. If you were to model the relationships between Parent
and Child, you'd say it was a many-many (a child must have at least one
known parent and a parent may have at least one child).

In our case, we may never know who the parents are. So if I'm right, a
child (I mean 'child' as in human child) record could not exist at the
many end of a relationship in a table where its parents' (I mean mother
and father) details were stored. In other words, I must be able to
create a child's record without first creating the parents' records.

So how would I model the relationship between child and parents here?
Would it simply be a one-to-one between child-mother and child-father?
That would lead to duplication in the case of parent's with more then
one child (i.e. in the case of a mother with two children, you'd
have two records for the mother in the mother table).

I'm not sure if I've made myself very clear here. If so, please let me know.

Can someone advise please? TIA.

#2

On 14/09/2011 10:16:01, Mo wrote:

Quote:

We have trial where young children are tested for their levels of
cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother and/or
father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia their
parents are also tested for the same condition. In the case that the
child's biological parents aren't known, we won't follow up with these
tests.

Here's the puzzle. If you were to model the relationships between Parent
and Child, you'd say it was a many-many (a child must have at least one
known parent and a parent may have at least one child).

In our case, we may never know who the parents are. So if I'm right, a
child (I mean 'child' as in human child) record could not exist at the
many end of a relationship in a table where its parents' (I mean mother
and father) details were stored. In other words, I must be able to
create a child's record without first creating the parents' records.

So how would I model the relationship between child and parents here?
Would it simply be a one-to-one between child-mother and child-father?
That would lead to duplication in the case of parent's with more then
one child (i.e. in the case of a mother with two children, you'd
have two records for the mother in the mother table).

I'm not sure if I've made myself very clear here. If so, please let me
know.

Can someone advise please? TIA.

I suggest 2 tables
TblPerson

#3

I suggest 2 table
TblPerson
PersonID Autonumber
Name, address etc.
Has hypercholesterolemia

TblJnPersonPerson
ChildID Long Joint Key
ParentID Long Joint Key
Relationship Byte ( 1 = Mother, 2 + Father, 3 = Guardian)

Then set up a relationship with 2 copies of the TblPerson (TblPerson &
TablPerson_1) and 1 copy of the TblJnPersonPerson Link the PersonID from
TblPerson to the ChildID and the PersonID from TblPerson_1 to the ParentID
HTH

Phil

#4

Mo wrote:

Quote:

We have trial where young children are tested for their levels of
cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother
and/or father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia their
parents are also tested for the same condition. In the case that the
child's biological parents aren't known, we won't follow up with these
tests.

Here's the puzzle. If you were to model the relationships between
Parent and Child, you'd say it was a many-many (a child must have at
least one known parent and a parent may have at least one child).

In our case, we may never know who the parents are. So if I'm right, a
child (I mean 'child' as in human child) record could not exist at
the many end of a relationship in a table where its parents' (I mean
mother and father) details were stored. In other words, I must be able to
create a child's record without first creating the parents' records.

So how would I model the relationship between child and parents here?
Would it simply be a one-to-one between child-mother and child-father?
That would lead to duplication in the case of parent's with more then
one child (i.e. in the case of a mother with two children, you'd
have two records for the mother in the mother table).

I'm not sure if I've made myself very clear here. If so, please let
me know.
Can someone advise please? TIA.

Any many-to-many relationship needs a "bridge" or "link" table to define the
links.
Your model is further complicated by the fact that a "Parent" is also a
"Child" of someone. Thankfully, you don't have to consider non-biological
relationships (step-parents, adoptive parents, etc.). So you really can't
think of having a Children table and a Parents table. You need to have a
single "People" table, each record identified by a PersonID. Then you would
create a ParentChild table to store the links. It would contain two fields:
ChildPersonID and ParentPersonID. Optionally, you can have a third field
called ChildParentID (autonumber) to be the table's primary key. This
approach would require you to create a second unique index on ChildPersonID
and ParentPersonID. Purists would say to skip the autonumber field and
simply create the Primary Key on ChildPersonID and ParentPersonID.

You would create two one-to-many links, one between PersonID and
ChildPersonID, and the other between PersonID and ParentPersonID. This
scheme allows you to create a Person record without creating a link to the
person's parent(s). Once you know what the links are, simply add records to
ParentChild to establish the links, on record for each parent. You should
probably have some VBA code to prevent the creaton of more than two linking
records (data macros in A2010 would allow you to create a trigger to perform
this function).

#5

Isn't this type of 'ancestral' data normally constructed with just one
table.
tblPersons: personID, motherID, fatherID?
(motherID, fatherID must exist as personIDs)

Jon

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

Quote:

Mo wrote:
We have trial where young children are tested for their levels of
cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother
and/or father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia their
parents are also tested for the same condition. In the case that the
child's biological parents aren't known, we won't follow up with these
tests.

Here's the puzzle. If you were to model the relationships between
Parent and Child, you'd say it was a many-many (a child must have at
least one known parent and a parent may have at least one child).

In our case, we may never know who the parents are. So if I'm right, a
child (I mean 'child' as in human child) record could not exist at
the many end of a relationship in a table where its parents' (I mean
mother and father) details were stored. In other words, I must be able to
create a child's record without first creating the parents' records.

So how would I model the relationship between child and parents here?
Would it simply be a one-to-one between child-mother and child-father?
That would lead to duplication in the case of parent's with more then
one child (i.e. in the case of a mother with two children, you'd
have two records for the mother in the mother table).

I'm not sure if I've made myself very clear here. If so, please let
me know.
Can someone advise please? TIA.

Any many-to-many relationship needs a "bridge" or "link" table to define
the links.
Your model is further complicated by the fact that a "Parent" is also a
"Child" of someone. Thankfully, you don't have to consider non-biological
relationships (step-parents, adoptive parents, etc.). So you really can't
think of having a Children table and a Parents table. You need to have a
single "People" table, each record identified by a PersonID. Then you
would create a ParentChild table to store the links. It would contain two
fields: ChildPersonID and ParentPersonID. Optionally, you can have a third
field called ChildParentID (autonumber) to be the table's primary key.
This approach would require you to create a second unique index on
ChildPersonID and ParentPersonID. Purists would say to skip the autonumber
field and simply create the Primary Key on ChildPersonID and
ParentPersonID.

You would create two one-to-many links, one between PersonID and
ChildPersonID, and the other between PersonID and ParentPersonID. This
scheme allows you to create a Person record without creating a link to the
person's parent(s). Once you know what the links are, simply add records
to ParentChild to establish the links, on record for each parent. You
should probably have some VBA code to prevent the creaton of more than two
linking records (data macros in A2010 would allow you to create a trigger
to perform this function).

#6

This is a definite possibility, but this design is not strictly "normal", in
that it has repeating data elements.

But given that we are concerned only with biological relationships, your
suggested design should work. You would still need to create similar
relationships between personid and motherid, and the other between personid
and fatherid. It would be very difficult to come up with any scenarios that
could not be modelled with this design.

It would be more problematical if non-biological relationships were being
modeled - think of a child of two male parents - one would be forced to
arbitrarily assign one to be the "mother" and the other the "father". Not to
mention the biological mother and/or father. Now, perhaps you see the value
of a separate parent-child table?

Jon Lewis wrote:

Quote:

Isn't this type of 'ancestral' data normally constructed with just one
table.
tblPersons: personID, motherID, fatherID?
(motherID, fatherID must exist as personIDs)

Jon

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in message
news:j4q227$jnl$1 (AT) dont-email (DOT) me...
Mo wrote:
We have trial where young children are tested for their levels of
cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother
and/or father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia their
parents are also tested for the same condition. In the case that the
child's biological parents aren't known, we won't follow up with
these tests.

Here's the puzzle. If you were to model the relationships between
Parent and Child, you'd say it was a many-many (a child must have at
least one known parent and a parent may have at least one child).

In our case, we may never know who the parents are. So if I'm
right, a child (I mean 'child' as in human child) record could not
exist at the many end of a relationship in a table where its parents' (I
mean
mother and father) details were stored. In other words, I must be
able to create a child's record without first creating the parents'
records. So how would I model the relationship between child and parents
here? Would it simply be a one-to-one between child-mother and
child-father? That would lead to duplication in the case of
parent's with more then one child (i.e. in the case of a mother
with two children, you'd have two records for the mother in the mother
table).

I'm not sure if I've made myself very clear here. If so, please let
me know.
Can someone advise please? TIA.

Any many-to-many relationship needs a "bridge" or "link" table to
define the links.
Your model is further complicated by the fact that a "Parent" is
also a "Child" of someone. Thankfully, you don't have to consider
non-biological relationships (step-parents, adoptive parents, etc.).
So you really can't think of having a Children table and a Parents
table. You need to have a single "People" table, each record
identified by a PersonID. Then you would create a ParentChild table
to store the links. It would contain two fields: ChildPersonID and
ParentPersonID. Optionally, you can have a third field called
ChildParentID (autonumber) to be the table's primary key. This
approach would require you to create a second unique index on
ChildPersonID and ParentPersonID. Purists would say to skip the
autonumber field and simply create the Primary Key on ChildPersonID
and ParentPersonID. You would create two one-to-many links, one between
PersonID and
ChildPersonID, and the other between PersonID and ParentPersonID.
This scheme allows you to create a Person record without creating a
link to the person's parent(s). Once you know what the links are,
simply add records to ParentChild to establish the links, on record
for each parent. You should probably have some VBA code to prevent
the creaton of more than two linking records (data macros in A2010
would allow you to create a trigger to perform this function).

#7

On 14/09/2011 13:16:01, "Bob Barrows" wrote:

Quote:

This is a definite possibility, but this design is not strictly "normal",
in that it has repeating data elements.

But given that we are concerned only with biological relationships, your
suggested design should work. You would still need to create similar
relationships between personid and motherid, and the other between
personid and fatherid. It would be very difficult to come up with any
scenarios that could not be modelled with this design.

It would be more problematical if non-biological relationships were being
modeled - think of a child of two male parents - one would be forced to
arbitrarily assign one to be the "mother" and the other the "father". Not
to mention the biological mother and/or father. Now, perhaps you see the
value of a separate parent-child table?

Worth adding that the 2 table approach allows an infinite number of
grandparents, great grandparents etc ... maybe back to Adam & Eve?

Phil

#8

You can equally do that in one table.

Jon

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

Quote:

On 14/09/2011 13:16:01, "Bob Barrows" wrote:
This is a definite possibility, but this design is not strictly "normal",
in that it has repeating data elements.

But given that we are concerned only with biological relationships, your
suggested design should work. You would still need to create similar
relationships between personid and motherid, and the other between
personid and fatherid. It would be very difficult to come up with any
scenarios that could not be modelled with this design.

It would be more problematical if non-biological relationships were being
modeled - think of a child of two male parents - one would be forced to
arbitrarily assign one to be the "mother" and the other the "father". Not
to mention the biological mother and/or father. Now, perhaps you see the
value of a separate parent-child table?

Worth adding that the 2 table approach allows an infinite number of
grandparents, great grandparents etc ... maybe back to Adam & Eve?

Phil

#9

"Jon Lewis" <jon.lewis (AT) cutthespambtinternet (DOT) com> wrote in
news:NKidnU2JEZTGCO3TnZ2dnUVZ8kadnZ2d (AT) bt (DOT) com:

Quote:

Isn't this type of 'ancestral' data normally constructed with just
one table.
tblPersons: personID, motherID, fatherID?
(motherID, fatherID must exist as personIDs)

Jon

No, because of limitations imposed in trying to link a table to

itself..

Persons table contains info for each individual.
Unions contains the data for two people in the same row.
Offspring contains data for the children of a Union

With one table it is impossible to properly construct a list of step-
brothers ( & sisters),

That may not be necessary in your application, but in other geneology
work it's very handy.

Bob

Quote:

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote in message
news:j4q227$jnl$1 (AT) dont-email (DOT) me...
Mo wrote:
We have trial where young children are tested for their levels
of cholesterol to determine if they have hypercholesterolemia.

The children visit the GP and may be accompanied by their mother
and/or father or a guardian (so neither parent).

If it turns out the child has (familial) hypercholesterolemia
their parents are also tested for the same condition. In the
case that the child's biological parents aren't known, we won't
follow up with these tests.

Here's the puzzle. If you were to model the relationships
between Parent and Child, you'd say it was a many-many (a child
must have at least one known parent and a parent may have at
least one child).

In our case, we may never know who the parents are. So if I'm
right, a child (I mean 'child' as in human child) record could
not exist at the many end of a relationship in a table where its
parents' (I mean mother and father) details were stored. In
other words, I must be able to create a child's record without
first creating the parents' records.

So how would I model the relationship between child and parents
here? Would it simply be a one-to-one between child-mother and
child-father? That would lead to duplication in the case of
parent's with more then one child (i.e. in the case of a mother
with two children, you'd have two records for the mother in the
mother table).

I'm not sure if I've made myself very clear here. If so, please
let me know.
Can someone advise please? TIA.

Any many-to-many relationship needs a "bridge" or "link" table to
define the links.
Your model is further complicated by the fact that a "Parent" is
also a "Child" of someone. Thankfully, you don't have to consider
non-biological relationships (step-parents, adoptive parents,
etc.). So you really can't think of having a Children table and a
Parents table. You need to have a single "People" table, each
record identified by a PersonID. Then you would create a
ParentChild table to store the links. It would contain two
fields: ChildPersonID and ParentPersonID. Optionally, you can
have a third field called ChildParentID (autonumber) to be the
table's primary key. This approach would require you to create a
second unique index on ChildPersonID and ParentPersonID. Purists
would say to skip the autonumber field and simply create the
Primary Key on ChildPersonID and ParentPersonID.

You would create two one-to-many links, one between PersonID and
ChildPersonID, and the other between PersonID and ParentPersonID.
This scheme allows you to create a Person record without creating
a link to the person's parent(s). Once you know what the links
are, simply add records to ParentChild to establish the links, on
record for each parent. You should probably have some VBA code to
prevent the creaton of more than two linking records (data macros
in A2010 would allow you to create a trigger to perform this
function).

--
Bob Q.
PA is y I've altered my address.

#10

On Wed, 14 Sep 2011 08:16:06 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Quote:

It would be more problematical if non-biological relationships were being
modeled - think of a child of two male parents - one would be forced to
arbitrarily assign one to be the "mother" and the other the "father". Not to
mention the biological mother and/or father.

This exact problem, from a legal and birth certificate perspective,
just came up in Saskatchewan, Canada.

Sask. same-sex couple breaks new legal ground

"An unidentified same-sex couple from Saskatchewan made headlines this
week over a precedent-setting family court judgment that paves the way
for both of them to be listed as the sole parents of their
two-year-old daughter on her birth certificate."

http://www.leaderpost.com/health/tec...333/story.html

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/