Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2008
    Posts
    1

    Smile database design help please

    Hai all,

    We have an application in which a database stores information about all residents of a particular state.The present table has fields representing person_id, state_id, district_id, house_id, family_id, person_name and some other fields.For the above table person_id forms primary key and it is a meaningful code formed by combining state_id, district_id, house_id, family_id and a two digit serial part.

    Now I would like to store information regarding relations among family members.And towards that I was thinking of keeping another table with fields (person_id, related_person_id, relationtype). relationtype will have values representing various relations like father, mother etc. For example, a row with values (A,B,0) may convey information as person A is the son of person B where 0 represents relationship son. Is there a better way to represent such an information. With the above table, if there are 4 members in a family, for each person there will be 3 rows in the above table giving relation of that person with the 3 other persons. Thus for the family, there will be 12 rows..

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vaisakha
    For the above table person_id forms primary key and it is a meaningful code formed by combining state_id, district_id, house_id, family_id and a two digit serial part.
    this design is going to give you nothing but problems

    what if a person moves to a different house? you will have to update the person's primary key, and this will cascade to all of the person's relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Apart from that, shouldn't district & state be part of the house entity.

    And what happens if someone lives half the time in one house and the other half in another? I've got my city penthouse, country manor and holiday apartment in Monaco - how could you record all that?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    You forgot about the chalet in Madrid I can still borrow that for a week in July, right?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One doesn't like to boast George - that would be vulgar.

    Of course you may. The only favour in return would be if you could meet up with my man Jose and bring some merchansi.... I mean some personal items back into the country with you.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    I think there are going to be other complications in the familai realtionship
    eg father/mother/stepfather/stepmother

    ..and thats before you start getting complex one offs where person A marries person B, has children and then divorces to marry person B's father/mother (or whatever thats called in the Southern US states these days

    as its currently modelled I reckon theres fewer than 12 realtionships

    husband / wife
    father child 1 'could be step father or father
    father child 2 'do
    mother child 1 'could be step mother or mother
    mother child 2 'do
    child 1 child 2 'could be sibling or stepsibling (gender should determine if brother or sister)

    how you would model say gay/lesbian parents (within the confines of father/mother), or say mother + boyfriend (or father + girlfriend.. unlikely in the current legal climate unless mum popped her clogs).. is their a relationship between the father and the mothers children(I'd hope not but y'never know these days)
    there is also the prospect of adoptive father/mother, and foster father/mother

    a person exists independently of where they live, and where they live determines all the rest of the stuff eg state

    the problem as I see it is how do you model a realtionship which may be transitory. Eg a person may move form house A to House B, they will still have any familial relationships eg sibling, parent or child, but they could loose/get rid of other relationships eg foster, step whatever. their relationship isn't determined by their residence.. its determined by their ties between them which in most cases will be lasting, irrespective of where they live or move to

    Id expect to see an intersection table identifying the relationship between each person, probably with a PK of PersonID:A PersonID:B and RealtionshipTypeID.

    I think you will probably need to allow more than one realtionship per person pairing. unless you want to rule it out because its not somethign you allow/recognise
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    state_id and district_id are probably redundant. I would guess that one is a child class of the other. Only the childID is necessary, as the parentID would be derived from it.

    Relationships between family members should be determined by their relationship in a tree structure, not by labels. You would just need a Gender column to differentiate father/mother, or daughter/son, etc.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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