Model a family tree database in Access 2007

Question

I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have several N:M relationships.

• A tribe has many clans (1:M)
• A clan has many families (1:M)
• A family has many Sims, and a Sim can belong to many families (M:N)
• A lot can have many families living on it (1:M)
• A Sim can be a member of many classes, and a class has many Sims (M:N)
• An era has many classes, and a class can be in many eras (M:N)

This is what my model looks like so far:

SIM
----
Sim_id - unique PK
Sim_fname - First name of the Sim
#misc other details which will be filled in
fam_id - FK to FAMILY
Is_heir - yes/no

TRIBE
-----
tribe_id - unique PK
tribe_name
founder_id - FK to SIM who is the founder of the tribe

CLAN
----
clan_id - unique PK
clan_name
founder_id - FK to SIM who is the founder of the clan

FAMILY
------    
fam_id - unique PK
partner1_id - FK to SIM who is the first partner in the relationship
partner2_id - FK to SIM who is the second partner in the relationship
start_day - Day the relationship began
clan_id - FK to CLAN the FAMILY belongs to
lot_id - FK to LOT on which the FAMILY lives
end_day - can be NULL
end_reason - reason why the relationship ended

ERA
---
era_id - unique PK
era_name

CLASS
-----
class_id - unique PK
class_name

LOT
---
lot_id - unique PK
lot_gen - generation number of the male head of the lot
tribe_id - FK to TRIBE 
lot_number - number of the lot in the sequence

linkSIM_CLASS
-----------
sim_id - FK to SIM, composite PK
class_id - FK to ERA, composite PK

linkCLASS_ERA
-------------
class_id - FK to CLASS, composite PK
era_id - FK to ERA, composite PK

An example will help illustrate it:

Caleb is the founder of the Cullen tribe. He is the founder of the first clan, and the head of the first family (together with his wife Calliope). Their firstborn is daughter Bethany, followed by triplets (daughter Ferial, son Flint and daughter Feena). Flint is the firstborn son, so he is the heir. As he is a Generation 2 male, he is the founder of the clan Flintese.

Flint moves into his own lot and marries Ferial, forming his own family. They have several children, and then Ferial dies. Flint then marries Rahab (from the Swan tribe), forming another family. He is also given a concubine, Cullen-Fer, forming the another family. He has multiple children with both females, and they all live on the same lot. Flint was born in the Neanderthal Era as part of the Standard Class. He currently lives in the Roman Era as part of the Plebian Class. I will add a picture of the tables populated with this example data once I gain enough rep to post images.

(Please ignore the fact that he married his sister, it's part of the Sims 2 challenge I'm doing). Yes, I am creating a database to track my Sims, it's a fun way for me to use my SQL skills and to learn new things. I would like to maintain a historical log of where the Sim has lived, the multiple families they belong to, the different eras they have lived in etc.

There will be other tables also, but these are my core tables, so I would like to finalise these ones before implementing the other tables, as those relationships are simpler. Is my structure as normalised and efficient as possible?

Answer 1

Start using a ERD tool, or use paper to draw the relationships, those you have already written.

For example, "A tribe has many clans" and "one clan, which belongs to one tribe" means there's a 1:N relationship between Tribe and Clan, so there should be a tribe_id in Clan table (FK to Tribe).

Another issue would be the "a family has many Sims" and "a Sim belongs to at least 2 families". You'll have to use a Sim_Family joint table for the many-to-many association between the Sim and Family but the "at least 2" is hard (or impossible?) to enforce with DRI alone.

After you have drawn all relationships between tables (entities), check if there are circular paths in the Foreign Keys. It's hard to deal with that and there's usually a way to avoid it by minor alterations in the design.

Answer 2

1. the linkSIM_FAMILY looks like strange. A SIM can be a father of a family if he is male or a mohter of a family if she is a female. This is the fam_dad_id and the fam_mom_id of the family table. (almost) every sim is child of exactly one family. if it is a son or a daughter is decided by the sex of the sim and not by a role that mut be defined for the child. so the sim table must contain a family_id. the linkSIM_FAMILY can be removed.
2. in your description a tribe has a founder a clan does not. in your table definitions this is vice versa