Re: Relationship(s) for human family structure

From: Neo <neo55592_at_hotmail.com>
Date: Fri, 17 Aug 2007 18:29:05 -0000
Message-ID: <1187375345.064644.71150@j4g2000prf.googlegroups.com>

> I have a database with individuals in it, and I'd like to store their
> kin relationships also.
> A quick, clumsy way of doing it would be to have three columns: two
> for individuals, and the third expressing their relationship (e.g. 'a
> is sibling of b', 'a is parent of b', etc).

How about the following?

T_Person
ID Name
1 john
2 mary
3 bob

T_Relation
ID Name
1 husband
2 wife
3 brother
4 sister

T_Relationship
P1_ID Rel_ID P2_ID

->john    ->wife          ->mary
->mary   ->husband   ->john
->mary   ->brother     ->bob
->bob     ->sister       ->mary

Query to find name of john's wife's brother? Something similar to below:

SELECT name FROM T_Person WHERE ID = (
SELECT P2_ID FROM T_Relationship
WHERE
Person1 = (SELECT P2_ID FROM T_Relationship

                 WHERE P1_ID= (SELECT ID FROM T_Person WHERE name =

"john")
AND (SELECT ID FROM T_Relation WHERE name="wife") ) AND Rel_ID = (SELECT ID FROM T_Relation WHERE name="brother")) Received on Fri Aug 17 2007 - 13:29:05 CDT