Normalization help please and an additional table.

[Nils Titley]

I am working on a family tree data base. I have the following tables

Tables Defined
NAME ADDRESS SPOUSE
*NameID *AddressID *SpouseID
AddressID Address AddressID
ParentID City WifeHusband
First State First
Middle PostalCode Middle
Madien PhoneNum Last
Last BirthDate
BirthDate BirthPlace
BirthPlace DeathDate
DeathDate CellPhone
MarriageDate WorkPhone
SpouseID EmailPers
DivorceDate EmailBus
MarriageDate2
SpouseID2
DivorceDate2
CellPhone
WorkPhone
EmailPers
EmailBus

The * is the key. The ParentID contains the NameID for the parent of the
person.

Are these normalized tables and do I need a relationship table?

Thanks

 

[vbasean]

try this:

dhttp://www.matterware.com/download.html

 

[Allen Browne]

Genealogies present some interesting problems. I suggest you might be better
off buying some genealogical software, or even searching for freebies.

The table you have is not normalized, and will have problems such as these:

a) You have repeating fields (e.g. MarriageDate, MarriageDate2)
To be normalized, these would need to be in a related table (where one
person can have many marriages.)

b) One ParentID field?
Having both a father and a mother is not uncommon.
Do you need to track non-biological parentage as well (step parents)?

c) The contact details might be better in a related table (e.g. so one
person can have multiple phone numbers of different types in a related
table, but there's still just one column to look in to find any phone
number) Similarly for emails.

d) AddressID
Are you storing only the current address?
Is there a need to store previous addresses too?
If current only, have you thought about what you intend for the current
address of a deceased person?

e) Maiden
Does the database cope with other change-of-name possibilities?
What about the changes of name for people who are married multiple times, or
alter their name by deed poll?

f) Spouse table?
One SpouseID field? Doesn't a marriage require 2 people?
Is the spouse also a person in their own right?
Wouldn't it be better to put all the persons into the one table, and then
record the marriages in a separate table?

Additionally, there are some names here that will cause you grief in Access,
such as Name and First. For a full list to refer to when designing tables,
see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Nils Titley]

Allen

a) Thanks for that suggestion. I will look at changing.
b) I am only concerned with the my relatives. My family tree. Which means
that ParentID refers to the parent that is a member of my family.
c) I will look at this change.
d) Current address only. There is no current address for the dead. Will
the ground some place.
e) I should have left out the Maiden. The last name for women in the family
will be taken from the Husband. That is why the spouse indicated a boolean
wife/husband.
f) Spouse has been handled. But I may need a separate spouse table.

I will look at the naming.

Thanks for you suggestions and assistance.

 

[Stefan Hoffmann]

hi Nils,

Are these normalized tables and do I need a relationship table?

What do you mean with "relationship table"?

SpouseID and SpouseID2 is normalized, but not logical correct. Take for
example Liz Taylor. You need a table for ordering them, with the date
ranges they where married, just modify your table:

SPOUSE: NameID1, NameID2, DateFrom, DateTill

The WifeHusband flag is not really necessary, as you should store the
sex in your NAME table.
DateFrom and DateTill are the same as your MarriageDate and DivorceDate.

You may consider storing an AddressID instead of a string for BirthPlace.
You may also consider renaming your table NAME (it is a reserved word,
which shouldn't be used) to PERSON.

*Phone and Email* in the NAME table makes no sense, they are contact data.

As long as you only use biological parents then you may use
ParentFatherID and ParentMotherID, which point to the PERSON table.

If you will also use parents regard to the law of succession, then you
need a table like SPOUSE for that. E.g. consider your father as married
again and you where adopted by your step-mother.


mfG
--> stefan <--
Newbie-Info http://www.doerbandt.de/Access/Newbie.htm