Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

What is the question? How to model SIM and FAMILY? What is a Sim? I suppose this is a person like Caleb or Calliope. Is this true?
–
miracle173Apr 12 '12 at 8:01

I'm sorry for not being clear on that point, a SIM is equivalent to PERSON, so it would have all the normal attributes such as date of birth, hair colour, eye colour, sex etc.
–
Cindy WilliamsApr 12 '12 at 9:21

what is the head of a family. Is this always the spouse? is a child always the child of a family or an it be the child of a male sim and a female sim that are not married?
–
miracle173Apr 12 '12 at 23:04

The head of each family is always a male i.e. If Caleb has 5 sons, at least 5 new families will be formed once each son has been assigned a female. I've been defining a FAMILY as when a male has a child with a female (whether the relationship between the two is a marriage/affair/concubinage/friendship/whatever), but I realise this is insufficient, as I might have a situation where the male's first wife cannot have children. Even if he has subsequent children with additional wives, it doesn't mean that he and his first wife don't constitute a family.
–
Cindy WilliamsApr 13 '12 at 5:07

2 Answers
2

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.

I have written down all the relationships, and created the ERD for it, with all the possible tables and FKs. I put the "at least 2" families there to illustrate the potential for the N:M relationship between SIM and FAMILY. If the SIM does not marry, he/she only belongs to the father's family, which would be a straightfoward 1:M, but seeing as most SIMs do marry, they will belong at least to their father's family, the new family created from their first marriage, and any other families formed from any subsequent marriages.
–
Cindy WilliamsApr 12 '12 at 9:20

Now if you try to put parent and marriage relationship, too, it's a great exercice!
–
ypercubeApr 12 '12 at 9:29

Oh, I didn't see the SIM_FAMILY table the first time. It looks fine. (I can see a few issues like that a Family can have many Sims with the same role. And I assume a family can have many daughters but not many fathers, so it would be a complex constraint.)
–
ypercubeApr 12 '12 at 9:39

Yes, I should have said "potentially 2 or more", I'll edit the question now. The "parent" and "marriage" relationships would have been my next task, as I would need to differentiate between the different roles within a "marriage" (the Sim could be husband/wife/concubine/mistress) and also the type of marriage (arranged/love/affair) etc.
–
Cindy WilliamsApr 12 '12 at 9:47

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.

in your description a tribe has a founder a clan does not. in your table definitions this is vice versa

1. Yes. The sim_role field was there to indicate the status of the Sim in relation to the family, but it does not make sense, because each Sim will have a different relation with each family member, and I don't want to be rigid in purely defining a Sim's role in relation to its parents. I do agree with you that once the fam_dad_id and fam_mom_id have been linked to a FAMILY, each additional SIM linked to that fam_id is automatically a son/daughter depending on their sex_id.
–
Cindy WilliamsApr 13 '12 at 5:17

2. I realise my description is a bit convoluted. The "founder" is a special instance which only occurs in the 1st and 2nd generations, all of which take place in Era 1. There are 5 founders in Generation 1, 1 for each of the 5 tribes. The Clan founders are all the males in Generation 2 (sons of the founders), regardless of who their mothers are. I should add a "tribe_founder_id" to TRIBE, as with CLAN.
–
Cindy WilliamsApr 13 '12 at 5:20