Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Design for Genealogy like database

    Hi,

    I am working on a database for a non-profit organization. We have people as individuals joining us but also whole families.

    We would like to be able to link the individuals into whole families and track the relationship between the individuals.

    Is there anyone that could help me with an initial design of tables and fields?

    We would like to pull out the type of relationship (mother,father,son...). How do you design it in a way that we don't have to input the type of relationship between every person separately?

    I have done quite a bit a search online regarding some data models for that need but very unsuccessful. Also I've read through quite a lot of posts on here but haven't found anything that seems to deal with this problem.

    I am quite new at this and would really appreciate any help.

    Thanks everybody.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jodel View Post
    We would like to be able to link the individuals into whole families and track the relationship between the individuals.
    seems legit

    Quote Originally Posted by jodel View Post
    We would like to pull out the type of relationship (mother,father,son...). How do you design it in a way that we don't have to input the type of relationship between every person separately?
    you cannot pull something out that you didn't put it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    Certainly you could establish just the parent/child relationships and then derive relationships such as cousin, nephew, grandparent from that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2012
    Posts
    5
    thanks for your reply guys.

    I've attached the design I have come up with so far but I am just not convinced and sure if that is it.

    Could you please help me on refining it a bit more.

    From what I understand from Blindmans post for the Role Type i would only need either Parent or Child. From there we could derive because we have the gender also in the database if it's the mother or father and also from the lines of relationships if they are brothers, sisters or possibly even cousin?
    Or do I have to define the role type more or is there another way to connect the different relationships within each family?

    I just don't think I should have to define each relationship between every person separately since that would make it a huge work for data entry and there has to be a way to derive it from it.

    Also does this design still work for when people get divorced or am I missing another table to be able to keep track of different families and relationships?

    Again just letting you know that I am still very new in this and self taught. I am doing this as a volunteer for a non-profit organisation and any help in this would be really much appreciated.

    Thanks everybody
    Attached Thumbnails Attached Thumbnails family members design.JPG  

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman is right, the minimal relationship needed is parentage, together with marriage/equivalent if divorces are to be included

    you mentioned that this is for a non-profit, which is probably what's driving your family-oriented design

    presumably a divorce/remarriage situation would be a new family id?

    what is the purpose behind this database? what is the non-profit hoping to track or to be able to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    Probably can't store parentage in the entity table itself, as the scope of this business requirements doubtless extends beyond simple biological parentage.
    You'll need a table of people, and then a table of relationships that establishes many-to-many relationships between people(child and spousal at least).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2012
    Posts
    5
    Ok thanks guys,

    Let me give this another try. I have added a Relationship table. Through the Relationship Types (married, sibling) i would then be able to identify the type of relationship between the two individuals.

    Would that do the trick or am I missing something?

    @r937 - First of all we have whole families working with us. We provide housing for them and also the billing is then affected by it. Now I understand that deals only with one aspect of the family and not divorce or other situation.
    We offer services to family in crisis in the city and work over a longer period with them. Unfortunately there come a lot of disputes in families with that and for us it's very helpful to know the legal standings of families when we advice them or to know what kind of institution to get involved in some of the issues.

    Since we are a non profit organisation we also rely heavily on relationships between us and supporters. For that reason it's always very important for us to also be able to know what are relationship between us and the supporter is. We have over 350 staff that are with us and over 30 different services we offer. That means often there is more then just one standing relationship between each of the supporters and for us it's important to track this so we can communicate more effectively about their interests and what they are helping us with.

    I thought the whole family - relationship setup in the whole database will also help me with all the other relationships that we want to track and it's easiest to describe.

    Hope that answers your question.

    thanks for your help guys
    Attached Thumbnails Attached Thumbnails family members design.JPG  

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    WAY too complicated.
    You should just need an Individuals table and a Relationships table. And in that table you shouldn't need to store any relationships except "Spouse" and either "Child" or "Parent" (not both).
    From that, all family relationships can be derived, though you may want to store additional data about the relationship such as Wedding date, divorce date, adoption date, etc depending on your needs.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

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