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.

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

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?

You forgot about the chalet in Madrid I can still borrow that for a week in July, right?

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.

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

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.