Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Unanswered: Help w data structure

    Hello,

    I'm a little stuck with data structure for a db I'm creating for a church. Everything flows nicely except for the geneology portion of the structure.

    I've drawn it out several times and am still unsure of how to handle family relationships.

    My initial thought is to have 3 tables: tblHousehold->tblMember->tblChildren

    However, the problems that I anticipate are: What if members also have parents whom are members? There would also be marriages between children etc. I'm also trying to anticipate non-traditional families (single parents getting married or whatever)

    I've designed myself into a circle here, so I'd much appreciate any direction or thoughts on this.

    (The db is designed for tracking donations, groups, education, etc)

    Thanks in advance,

    Chris

  2. #2
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Are you dealing w/ divorces and remarriages? Kids names different than parents? This could be an endless circle of re/designs, but you seem to want to track donations, groups etc from/of members. So, it looks like your data is all about a single member. Last, First, middle names, address, etc. all simple mostly. Then how do ya get the geneology? Everybody has at least one father and one mother, although some exceptions are adopted/divorced folks. Perhaps a separate parents field (mom/dad) may help group families better, esp. when query group by same lastname or parent. Also, how much do you need to get out of your db for reports. It is a very complex task. Good luck.

  3. #3
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Thanks,

    Yep, you're correct...the most critical data flows nicely (donations, groups, etc).

    The pastor would like (in addition to all that) some type of Pastoral Care form which would allow him to know who belongs to who and how. So yeah, it gets messy. (Divorces, remarriages blah blah) It's about 2000+ people, so anything's possible.

    It may be more than I'm willing to tackle, but thanks for your input. After staring at it for a while, I was wondering if there was a simple answer that I was overlooking.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    interesting! does this do it?


    tblHousehold:
    IDhousehold autoPK
    address etc


    tblPerson:
    IDperson autoPK
    IDhousehold 'FK on tblHousehold
    name etc


    tblParentEntity:
    IDparentEntity 'duplicates allowed!!
    IDperson 'FK on tblPerson
    ...allows any number of members in a "parent entity"


    tblKids:
    IDkids autoPK
    IDparentEntity 'FK on tblParentEntity
    IDperson 'FK on tblPerson



    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Izy,
    You seemed to put in table design what I had trouble conveying in words. Good job. I think that is awesome. The foreign keys and multiples in the parent table really help all my babble make sense more clearly than I've seen yet. I just might try my geneology program in the near future.

    Thanks, also.

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Quote Originally Posted by izyrider
    interesting! does this do it?


    tblHousehold:
    IDhousehold autoPK
    address etc


    tblPerson:
    IDperson autoPK
    IDhousehold 'FK on tblHousehold
    name etc


    tblParentEntity:
    IDparentEntity 'duplicates allowed!!
    IDperson 'FK on tblPerson
    ...allows any number of members in a "parent entity"


    tblKids:
    IDkids autoPK
    IDparentEntity 'FK on tblParentEntity
    IDperson 'FK on tblPerson



    izy
    Sorry for not responding earlier...out with the dang flu.

    Izy, that's very clever and very awesome. That'll do exactly what I need.

    Thanks, both of you, for taking a look at this.

    Chris

Posting Permissions

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