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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

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

    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
    Recognized Expert Top Contributor
    • Mar 2007
    • 1726

    #2
    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
    Code:
    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

    Comment

    • NeoPa
      Recognized Expert Moderator MVP
      • Oct 2006
      • 32633

      #3
      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.

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        Here's how I would lay it out.
        Code:
        [b]tblPerson[/b]
        PersonID PK
        FirstName
        LastName
        Gender
        
        [b]tblRelationship[/b]
        PersonID1 FK
        PersonID2 FK
        RelationshipID FK
        
        [b]tblRelationshipTypes[/b]
        RelationshipID PK
        RelationshipDesc

        Comment

        • thesmithman
          New Member
          • Aug 2008
          • 37

          #5
          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.

          Comment

          • Seth Schrock
            Recognized Expert Specialist
            • Dec 2010
            • 2965

            #6
            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.

            Comment

            • NeoPa
              Recognized Expert Moderator MVP
              • Oct 2006
              • 32633

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

              Comment

              • Hennepin
                New Member
                • Oct 2009
                • 25

                #8
                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

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32633

                  #9
                  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.
                  Last edited by NeoPa; Jun 24 '11, 09:21 PM.

                  Comment

                  • OldBirdman
                    Contributor
                    • Mar 2007
                    • 675

                    #10
                    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).

                    Code:
                    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.

                    Code:
                    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.

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

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32633

                      #11
                      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.

                      Comment

                      • OldBirdman
                        Contributor
                        • Mar 2007
                        • 675

                        #12
                        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.
                        Code:
                        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
                        Code:
                        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
                        Code:
                        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?

                        Comment

                        • NeoPa
                          Recognized Expert Moderator MVP
                          • Oct 2006
                          • 32633

                          #13
                          Originally posted by OldBirdman
                          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.

                          Comment

                          • OldBirdman
                            Contributor
                            • Mar 2007
                            • 675

                            #14
                            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.

                            Comment

                            • Rabbit
                              Recognized Expert MVP
                              • Jan 2007
                              • 12517

                              #15
                              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.

                              Code:
                              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.

                              Comment

                              Working...