Re: Relationship(s) for human family structure
Date: Fri, 17 Aug 2007 18:29:05 -0000
> 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?
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
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 - 20:29:05 CEST