Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: multiple relations ships

    I have been working on developing genealogical database. One of the things I would like to do is show a family tree like relationship between generations. For example Generation 1 could have multiple generation 2's, and each generation 2 could have many generation 3's. ultimately I would like to develop this to the point that if I want to create a report for someone in Generation 6 that will also give me a listing of blood line parent back to generation 1. Is this best done using a separate table for each generation each linked to each other with each table having a one to many relationships for each successive table? Or is there a simpler way to link one generation to the next?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Coming from a manufacturing background, I would think of this as two tables: an item master and an assembly parts list.

    The item master lists every single item in the book. In your case, each person would be given an ID number (autonumber) in the (for example) tblPeople. The record would include all his particulars: name, gender, date of birth, location, ad infinitum.

    The parts list (for example, tblTreeData) has a single item from tblPeople, given an ID number (autonumber). Every person from tblPeople that is on the tree can be added to the list by adding his id number as a foreign key. You also need additional fields to identify generation, relation (spouse or sibling), etc.

    Think this way: I myself am a member of a family tree on a genealogy website that has over 2,000 people, and at least 7 generations, on the tree.

    Take a look at some of the genealogy websites for some ideas as to format, etc.

    Good luck,

    Sam

  3. #3
    Join Date
    Jul 2011
    Posts
    5
    Which sites do you suggest I look at that may show their table structure and relationships?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Google "genealogy" and/or "family tree" for some good ones.

    Sam

    BTW, don't even think of doing this without a good data-entry form. You'll go bonkers without one!
    Last edited by Sam Landy; 07-12-12 at 23:25. Reason: added a postscript

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    From a database perspective, in its simplest form, you only need to know the items parent to create the tree hierarchy. This works great for a list of parts, but families are not that simple and therefore neither is the structure.

    A person can have biological parents and adoptive parents. The adoptive parents could get divorced and remarried creating more decisions. There are people who are married, divorced, and remarried to a sibling of their first spouse. We have single parent families where one parent is unknown, families with two parents of the same sex, and children who live in foster families. There are family relationships that don't even have proper names... what do you call the children of your siblings spouse from a previous marriage?

    The point is, you can make this as complicated or as simple as you want. You just need to be aware that when you go for a simpler path you may end up with scenarios that don't fit your database. The problem occurs when people decide to remove the database constraints to fit in data that the system wasn't designed to hold.

    Steve

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Warden View Post
    Which sites do you suggest I look at that may show their table structure and relationships?
    this one: Categories and Subcategories

    what you call blood line, it calls breadcrumb
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •