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?