Normalization For Family Groups

[Jonnip]

Hi there, i am new to this db stuff so please be patient!
I am making a database that is holding member data among other things,

one of my tables is tblMember
tblMember

• PK-MemberID
• f_name
• l_name
• hnd_phone
• hme_phone
• gender
• address
• FK-postcode

I have a tblPostcode that has all the postcodes for the area i am interested in and this brings up the town and area information based on the postcode.

Now this DB has some members that are of the same family group, and last name therefore sometimes will be duplicated. however sometimes there are last names that are common that 2 different families will have the same last name.

Firstly how can i make this properly normalized or do i ignore it in this case

Secondly how can i create family groups, a simple query wouldnt work i dont think as "smith" could bring up 5 different families!

thanks for your help and time

Jonni

 

[Galaxiom]

The simple way is to appoint one person as the seed member of the family.
Include a Family field which records the MemberID of the seed member of their family.
The seed member would refer to their own ID.

Query the family field to get all members of a family.

In a more sophisticated system, people can be in two families. eg Parent family and a family with their children. To record this use a join table to record relationships between the MemberID and the seed member of any family they belong to. They have one record for each family they belong to so you can have any number of them. Handy if someone marries often.

Also if you like, instead of the seed you can have a table just for families and record the FamilyID in the Member table Family field.

 

[georgedwilkinson]

Simple is good. But, here is something that will make you think a little more about the whole thing: http://www.tdan.com/view-articles/5014

 

[Jonnip]

thanks guys, bookmarked the article and reading it now. will post back soon

Jonni

 

[Jonnip]

Hi,
I have bookmarked the link you gave me george, it is very interesting but a little complicated for me at this stage and also i think it is slightly overkill for the current application.

I will work out what i will do and then post back.

thanks again.
Jonni

 

[georgedwilkinson]

No big deal. Just wanted to let you and anybody who runs across this thread that this could be a very big issue.