Database model for representing families, households and relationships
Here is my situation. I am creating a database to track relationships between people and households. As a rule, everything is tied to the “head of household”.
I try to avoid this because it creates problems when people move around (i.e. brother finally gets a job and leaves) or families break up (i.e. dad and mom get divorced, household is now split in two, some children stay with mom, some with dad).
The huge challenge is figuring out how to cascade the data when the model is based on HOH. My approach breaks down the data into 3 tables, Household, Person and Relationship. hh just stores the address and id. relationships store person_id_a, person_id_b and the relationship code (i.e. 1 = sibling). And the person stores names and hh_id. That way, if the family breaks up, I can change the relationship between mom and dad to NULL and create a new hh for dad and all the kids who came with him without breaking other relationships. [This is what the model looks like:
Person -person_id -hh_id
Relationship -person_id_a -person_id_b -relationship_cd
Household -hh_id -address
Does this make sense to you guys? Can you think of any reason why this won't work, or think there is a better model?
Sorry, I know this is a little depressing. All these divorces make my job difficult> :(
source to share
For names, I recommend keeping it simple. Enter a table of names with three columns:
- Specific names
- FamilyNames
- NameType {legal, alternate}
You can even just go with a single name column for FullName. I wouldn't have a middle name, married name, maiden name, paternal name, nickname, or any other "special" name columns. This will only complicate any name lookup algorithm and confuse data entry. Here are some examples to consider:
- John Paul Smith
- Mary Paul Smith
- John Henry William Artemis Williams
- Maria de los Angeles Gomez Portillo
- Abdul Rahmin ibn Said ibn Abd al-Aziz al-Filastini
- Abdulla
So what do these names show? The first is a common English name, the first (John), the middle (Paul) and the last (Smith). The second is Mary. She has no name in her legal title. Her maiden name is Paul and her married name is Smith. She uses both, without the hyphen. The third is John. His parents thought it would be great to give him three middle names. The fourth is Hispanic name and full legal name. Her name is Maria de los Angeles. "Los Angeles" is part of her first name. She has no middle name. Her first father's surname was Gomez. Her mother's first surname was Portillo. So, Maria is the full name of Gomez Portillo. She can usually only go to Maria Gomez because she's so tired of having her name mis-typed. Technically, Portillo is her "last" name,but if she uses only one of her two surnames, she will use the first (father's surname). Fifth - Abdul Rahmin, son of Said, grandson of Abd al-Aziz, Palestine. Have fun putting this first / middle / last. Abdul Rahmin is his name. All the rest are surnames. The last one is Abdullah. He is from Afghanistan. It's not uncommon for people who don't have a last name. He's just Abdullah. He also doesn't know his date of birth because where he was they don't care about it (this happened a lot in American colonial times ... caring about birth dates is kind of a recent thing in many cultures).Abdul Rahmin is his name. All the rest are surnames. The last is Abdullah. He is from Afghanistan. It's not uncommon for people who don't have a last name. He's just Abdullah. He also doesn't know his date of birth because where he was they don't care about it (this happened a lot in American colonial times ... caring about birth dates is kind of a recent thing in many cultures).Abdul Rahmin is his name. All the rest are surnames. The last is Abdullah. He is from Afghanistan. It's not uncommon for people who don't have a last name. He's just Abdullah. He also doesn't know his date of birth because where he was they don't care about it (this happened a lot in American colonial times ... caring about dates of birth is kind of a recent thing in many cultures).
You must have a separate name table, so a person can have multiple name strings. The example illustrates one of many, many reasons. Mary Smith hates her common name. Thus, she gets him legally changed to Countess of the Sunshine Universe Lollipop. She regrets that two years later. She now sometimes uses Mary Smith and sometimes the Solar Universe, depending on the phase of the moon. Keep both of them and you will find it anyway.
However, you do this, people will mutilate names in every way imaginary (and some you have no idea) and enter them in different ways at different times. If you don't take it for granted, you're in trouble.
For households, as I mentioned in my comments, you can maintain a many-to-many relationship. A household can have zero for many people and a person can have zero for many households. This is a little tricky because it depends on what you define as a household. You may feel the need to label it as primary, but think of a child who spends half of his time with his mother and half with his father. Which one is primary?
source to share