For names, I recommend keeping it simple. Have a name table with three columns:
- GivenNames
- FamilyNames
- NameType {legal, alternate}
You can even just go with a single name column for FullName instead. I would not have middle name, married name, maiden name, paternal surname, nickname, or any other "special" name columns. It will only complicate any name searching algorithm, and confuse data entry. Here are some example names to consider:
- John Paul Smith
- Mary Paul Smith
- John Henry William Artemis Williams
- Maria de los Angeles Gomez Portillo
- Abdul Rahmin ibn Saeed ibn Abd al-Aziz al-Filasteeni
- Abdulla
So what do these names show? The first is pretty normal english name, with first (John), middle (Paul) and last (Smith). The second is Mary. She has no middle name in her legal name. Her maiden surname is Paul, and her married surname is Smith. She uses both, without a hyphen. The third is John. His parents thought it would be awesome to give him three middle names. The fourth is a Hispanic name, and that's her full legal name. Her given name is Maria de los Angeles. "de los Angeles" is part of her fist name. She does not have a middle name. Her father's first surname was Gomez. Her mother's first surname was Portillo. So Maria's full surname is Gomez Portillo. She may commonly go by just Maria Gomez because she's so tired of her name getting entered in a mangled way. Technically, Portillo is her "last" name, but if she were to use only one of her two surnames, she would use the first one (the paternal surname). The fifth is Abdul Rahmin, son of Saeed, grandson of Abd al-Aziz, of Palestine. Have fun putting this in first/middle/last. Abdul Rahmin is his given name. All the rest are family names. The final one is Abdulla. He's from Afghanistan. It's not uncommon for folks to have no last name there. He is simply Abdulla. He also doesn't know his birthdate, because where he was from, they don't care about that (this was often the case in American colonial times...caring about birthdates is kind of a recent thing in many cultures).
You should have a separate name table, so a person can have more than one name row. An example illustrates one of the many, many reasons why. Mary Smith hates her common name. So she gets it legally changed to Sunshine Lollipop Countess of the Universe. She sort of regrets that two years later. Now, she sometimes uses Mary Smith, and sometimes Sunshine Universe, depending on the phase of the moon. Store both of them and you find her either way.
However you do it, people will mangle names in every way imaginable (and some you wouldn't imagine), and enter them differently at different times. If you don't accept that as a given, you'll have problems.
For households, as I mentioned in my comments, you may want to support a many-to-many relationship. A household can have zero to many people, and a person can have zero to many households. This is a little tricky though, because it depends on what your define as a household. You may feel the need to denote one as primary, but consider a child that spends half her time with her mother, and half with her father. Which one is primary?
