Normalisation Problem? (1 Viewer)

[majhl]

Hello All,
Here is the situation:
A parent can have many relations (maternal- and paternal grandparents, siblings, father and mother and children). Each of these relations can in turn have zero or more medical conditions. Any single medical condition can be assigned to zero or more relations. OK so far?

I have four tables in my design:

Parent - PK ParentID
Relations - PK RelationID, FK ParentID, Relation
RelationsAndConditions - PK RelationID, PK ConditionID
Conditions - ConditionID, Condition

I'm not sure this is correct. Under the current design if I inserted a record for ParentID = 1 into the relatives table for Maternal Grandparents, the data would look like this:

RelationID Relation ParentID
1 Maternal grandparent
2 Paternal grandparent
3 Father
4 Mother
5 etc...
6

So what would happen to the table if I wanted to insert ParentID = 2 for Maternal grandparent? Obviously there's a problem somewhere. Do I need to further normalise the relations table so that it looks like this:

Relations - PK RelationID, Relation ParentsAndRelations - PK Relation ID, PK ParentID

Would RelationsAndConditions look like this?

RelationsAndConditions - PK RelationID, PK ParentID, PK ConditionID

This is getting very confusing as I'm having trouble visualising how a form-subform would work, if it can be represented by such a structure. Can anyone advise please, either with the normalisation of the tables or the design of a form/subform that would allow for the situation I'm facing.

Thanks for any help.

 

[michaeljryan78]

You may be able to just have a table with personID, first, last, parent (where the parent is the personID of another user in the same table). You can self join that table to gather the hierarchy.

 

[JohnLee]

Hi,

Thoughts off the top of my head are that you need possible 4 tables as follows:

Table 1 [People - Names (Forename, Surname etc)]

Table 2 [People Relationships - Grand Father, Grand Mother, Mother, Father, Brother, Sister, Husband, Wife etc)

Table 3 [Medical Conditions]

Table 4 Link table [to link all the relationships to each other] You would have a Primiary Auto ID Key for this table and the Primary Key from each of the other tables as Foreign Keys in this table to link them to each other.

There may be others with more experience and knowledge than me that might suggest something different, but these are just my initial thoughts based on the information supplied.

I hope this helps.

Regards

John

 

[majhl]

You may be able to just have a table with personID, first, last, parent (where the parent is the personID of another user in the same table). You can self join that table to gather the hierarchy.

Hello,

Thanks for the reply.

I'm not sure if I'm familiar with the solution you're describing. Can you elaborate please?

 

[gemma-the-husky]

the way to do this is not to store the grandparent, or siblings etc specifically.

all you need to do is store the parents of any individual. the grandparents are then the parents of the parents. Siblings/(or even half-siblings) are just other indivduals with the same parents, You can trace a family tree completely with this setup.

structures of this type are recursive in nature. effectively all you need is code to find your parents (or children), and then apply the same code to each generation retrieved by the process.

 

[majhl]

Hi,

Thoughts off the top of my head are that you need possible 4 tables as follows:

Table 1 [People - Names (Forename, Surname etc)]

Table 2 [People Relationships - Grand Father, Grand Mother, Mother, Father, Brother, Sister, Husband, Wife etc)

Table 3 [Medical Conditions]

Table 4 Link table [to link all the relationships to each other] You would have a Primiary Auto ID Key for this table and the Primary Key from each of the other tables as Foreign Keys in this table to link them to each other.

There may be others with more experience and knowledge than me that might suggest something different, but these are just my initial thoughts based on the information supplied.

I hope this helps.

Regards

John

Hello John,

Thanks for the reply.

I think this is pretty much what I described, unless I'm missing something?

 

[michaeljryan78]

So you have michaeljryan78 as a personID, lets say. The parent is also listined in the table with a PersonID of Majhl. If Majhl is the parent of Michaeljhryan78 then the michaeljryan78 record looks like:

PersonID: Michaeljryan78
First: Michael
Last: Ryan
Parent : Majhl

Majhl Looks like:

PeronID: Majhl
First: Maj
Last: Hl
Parent: (you parent or null if that is the top of the tree)

 

[majhl]

the way to do this is not to store the grandparent, or siblings etc specifically.

all you need to do is store the parents of any individual. the grandparents are then the parents of the parents. Siblings/(or even half-siblings) are just other indivduals with the same parents, You can trace a family tree completely with this setup.

structures of this type are recursive in nature. effectively all you need is code to find your parents (or children), and then apply the same code to each generation retrieved by the process.

Hello gemma-the-husky and thanks for the reply,

This sounds intriguing. Can you perhaps describe what the table structure would look like?

Thanks again.

 

[michaeljryan78]

You can then drag that table into the QBE 2 times and Outer Join the Parent to the PersonID to get all relavent information. Allen Browne has a great explaination here:

http://allenbrowne.com/ser-06.html

 

[majhl]

You can then drag that table into the QBE 2 times and Outer Join the Parent to the PersonID to get all relavent information. Allen Browne has a great explaination here:

http://allenbrowne.com/ser-06.html

Thanks Michael. I'll try that out and see if it works for me.

 

[gemma-the-husky]

michael actually suggested this in the first reply to your post.

of course, you do need 2 parents per person. a mother and a father.

it's just the principle of the thing. so that you don't store a person's children. you just store the fact of who the parent is, for any given person. Then you can find all the children by a query that retrieves all people who have person x as a parent.