How should I link husbands, wives, and kids to each other?

[Seth Schrock]

I'm creating a database that will be used as a church directory. Among other things, they are wanting to keep track of spouses and kids. I don't want to have a husband first name field and a wife first name field in the same table (normalization theory), but I don't know how to put them in separate tables since they will also be listed individually. Can I use a combo box for the spouse and create a subform based on the same table to link the kids (since you don't know how many kids they have, a datasheet subform seems the way to go). The problem is there isn't a primary key/foreign key combination to link if the subform is based on the same table as the main form.

Maybe I should be asking if this is possible. I know that I could just type the names of the spouse and kids in textboxes without having them linked to any table, but that really defeats the purpose of the database.

[code green]

I don't want to have a husband first name field and a wife first name field in the same table (normalization theory),

Why not?
I am guessing you are worried about repeated surnames. All families do not have unique surnames.
I would use a 'person' table listing all personal information.
Names, DOB, birthplace... all common to all persons.
It makes less sense to have them in seperate tables.

Each could have an ID, that could appear in a 'family' table looking something like

husband_id    wife_id    child_id
    00001    00005    00007
    00001    00005    00008
    00001    00005    00009
    00002    00011    00017

There could also be a marriage table, address table.
Bear in mind that there may be divorces and remarriages and change of names within families

[NeoPa]

Rather than [ChildID]s, I'd use [ParentID]s instead. Rather than [HusbandID] and [WifeID] I'd use [SpouseID] instead. If you want something to indicate the gender of the spouse then that can be stored separately, but I suspect each Person record would have its own gender indicator of some sort, so that could be used to determine the type of spousal relationship.

[Rabbit]

Here's how I would lay it out.

[b]tblPerson[/b]
PersonID PK
FirstName
LastName
Gender

[b]tblRelationship[/b]
PersonID1 FK
PersonID2 FK
RelationshipID FK

[b]tblRelationshipTypes[/b]
RelationshipID PK
RelationshipDesc

[thesmithman]

One question to ask when evaluating the utility of any method, is how long you intend this system to be in place? If the children grow up and have kids of their own, then the parents become grandparents and the children become parents and you have to do some rearranging. Furthermore, if parents get divorced (and that does happen, even in church communities) then you have to be able to separate them from each other while retaining their relationship to the children. One suggestion might be, you could just have a long "people" table, and a second relational table describing the relationships between the people. That's getting quite similar to Rabbit's proposal, which may be the best suggestion for what you're trying to do.

[Seth Schrock]

I will try your answers. I thought I was going to have time to work on it, but then I got busy again. Hopefully next week...

This one is a personal database, so I can't do anything on it while at work.

[NeoPa]

No worries Seth. Whenever you're ready :-)

[Hennepin]

I have started a database to help with genealogy this is how I have set up my tables for family and individuals.

Table: Individuals
ind_Id Key
ind_name
ind_surname
ind_gender

Table: Family
fam_HusbID key linked to ind_Id
fam_WifeID key linked to ind_Id
fam_ID unique foreign key to fc_FamID

Table: Family_Chidren
fc_FamID key
fc_IndID key linked to ind_Id

[NeoPa]

As a simple illustration of some of the concepts this may be helpful. However, I'm not sure this structure would be appropriate even for what you describe (though that is not a discussion for this thread). It certainly doesn't seem to be a reliable suggestion for this question as it would not handle what is required of it.

[OldBirdman]

I see that most of the suggestions are to create a primary table of individuals (persons), I do not agree. I would make the primary table Family, and that Family be a self-defined unit. Pat introduces themself and then introduces their "Family", which might include the children, spouse, and Granny (who lives in the guest house behind the main house).

tblFamily
FamilyID    autonumber
MailAddr    Text
PhysicalLoc Text
. . . .

I would next create a table for individuals. Each individual would be in a "Family", even if that Family consisted of a single ndividual.

tblPersons
PersonsID    autonumber
LinkFamily   Long Number (Foreign Key)
FName        Text
LName        Text
Gender       Number or Text
Relationship Text
. . . .

Relationships would be HeadHousehold, Spouse, or Child. I made this field text. The 3 descriptions would be meaningful to the program. I could enter anything else, like Aunt, Grandpa, Cousin, etc. These would not be meaningful, so Nanny could be part of a Family and would be related the same as Uncle, Niece, or Granddaughter.

If a child were to marry, their foreign key would be changed to the new Family. The new spouse would be added to tblPersons, if they did not already exist. This arrangement should cover ALL "Families", whether the head of household is married, living with spouse, gay/lesbian, adopted children, multi-generations in household, or ...

This would address the stated problem. It would not address all issues that might arise. If someone in a Family is hospitalized or dies, the church might want to contact other Families to offer support or condolances. A super-Family table, or Clan table might be needed.

tblClan
ClanID       autonumber
LinkFamily1  Long Number (Foreign Key)
LinkFamily2  Long Number (Foreign Key)

[NeoPa]

A problem is that the layout you suggest doesn't seem to fit the question OB. Families can, and do, reside in multiple locations. Family relationships do not easily fit within the structure you've described.

[OldBirdman]

If the focus is on the individual, then the only relationship that is meaningful is Male<->Female->Child. This means that 2 sisters, living together with the child of each, as a "family", must be defined by finding the common parent(s), whether living or not, if even known.
Today's science says that the parents don't even have to have met.

The OP original question is "How should I link husbands, wives, and kids to each other?" Also, the data must be normalized.

Families today do not all have a husband, or a wife, or any kids. Spouses last names may not be the same, and the kids may have different last names from their parents. Marriage, divorce, custody, and death scramble the "traditiona l" family. Societies have different methods for defining family after such events.

I sensed, perhaps wrongly, that the OP was referring to a nuclear family, not an extended family. His phrase "...they are wanting to keep track of spouses and kids" implies this. No mention of more distant relations.

In the below, Lynn & Pat are Husband/Wife with 2 children, Ariel and Katie. Katie has married and is living with her husband in the guest cottage behind the main house. She lives with her husband, Sam.
Lynn's mother, Heloise, moved in when Katie moved out. Heloise needs some help with certain things, and cannot live alone.
Mark lives on his family farm with his son Mark.

tblClan links Lynn & Pat with their daughter & son-in-law.

tblFamily
 65 Star Route 22, Box 9 South of town on Road 8
141 123 Main Street      123 Main Street
688 123-B Main Street    123 Main Street-Guest House

tblPersons
  3  141  Pat      Stone    F  HeadHousehold
  4  141  Lynn     Stone    M  Spouse
 16  141  Ariel    Stone    F  Child
 17  688  Katie    Carter   F  Spouse
 29  141  Heloise  Martin   F  Grandmother
 156  65  Peter    Famme    M  HeadHousehold
 157  65  Mark     Famme    M  Child
 199 688  Sam      Carter   M  HeadHousehold

tblClan
  77  141  688

I would agree that child away at school or military would belong with the local family. So if Hal is away at school, he might still be part of family #65. His mail address might differ. What scenario won't fit this design?

[NeoPa]

OldBirdman:
I would agree that child away at school or military would belong with the local family. So if Hal is away at school, he might still be part of family #65. His mail address might differ. What scenario won't fit this design?

What part of that do you need explaining? A child away at school, or even grown up and married themself, is still part of a family. They generally have two parents (not necessarily still living) and potentially a spouse but the structure you have may indicates that a person is living at an address that they're simply not living at. Essentially, having a field of address info related to an ill-defined concept of a family is not logical, and in database work, that finds you out eventually and punishes you. A normalised database cannot be designed that way.

That said, and I wanted to answer your challenge, this is taking us off the topic of the thread. I'm happy to continue the discussion via PM if you like (and as it's you and we've discussed various things over a few years, via Skype also if you like). We've both expressed our views on the matter publicly, but I see no benefit to anyone in our continuing this discussion here.

[OldBirdman]

My apologies for getting off-topic. I did not intend that, nor do I want to confuse or mislead anyone using this forum to receive assistance or advice.

[Rabbit]

The family table only serves to further complicate the structure. With a two table structure, a person table and a relationship table, all family relationships can be inferred.

PID PName
1   Bob
2   Sally
3   John
4   Andy
5   Jenny

PID1 PID2 Relationship
1    2    Married
1    3    Child
2    3    Child
1    5    Divorced
1    4    Child
5    4    Child

This is all the information that is needed to infer all relationships. Of course, this structure is not adequate for a full family tree. If something of that scope is needed, then you would need to incorporate modified preordered tree.