On 24 Feb 2011, at 15:20, Simon Slavin wrote:
> On 24 Feb 2011, at 3:18pm, Philip Graham Willoughby wrote:
>
>> On 24 Feb 2011, at 14:07, Simon Slavin wrote:
>>
>>> A more flexible way to lay out genealogy databases is as follows:
>>>
>>> Person: id, name, sex, dob
>>>
>>> Relationship: id1, id2, connection
>>>
>>> So you end up with things like
>>>
>>> 1, 17, grandparent
>>> 1, 2, parent
>>> 1, 3, spouse
>>
>> Ewww that's nasty; I don't like the duplication of data which is implied by
>> other data, because it is easy to construct an inconsistent situation.
>> Either grandparent relationships are derived from parent relationships (in
>> which case why not make a view to do that) or they are entered separately
>> and there is scope for error.
>
> Oops. You're dead right: grandparent relationships should be derived. I
> wasn't thinking
However, that structure might make the cousins query significantly simpler and
it is easy to create views for all the derived properties (or temporary tables
if they're going to be queried a lot and not updated). For the OPs problem one
could use:
CREATE VIEW Parents AS
SELECT Child.personID AS ChildID,
Parent.personID AS ParentID
FROM Children AS Child
INNER JOIN Partners AS Parent ON Child.CoupleID = Parent.CoupleID;
CREATE VIEW Siblings AS
SELECT DISTINCT (Child1.ChildID AS ChildID, Child2.ChildID AS SiblingID)
FROM Parents AS Child1
INNER JOIN Parents AS Child2 ON (Child1.ChildID != Child2.ChildID AND
Child1.ParentID = Child2.ParentID);
CREATE VIEW Grandparents AS
SELECT DISTINCT (Child.ChildID AS GrandChildID, Parent.ParentID AS
GrandParentID)
FROM Parents AS Child
INNER JOIN Parents AS Parent ON (Child.ParentID = Parents.ChildID);
CREATE VIEW Cousins AS
SELECT DISTINCT (Child1.ChildID AS ChildID, Child2.ChildID AS CousinID)
FROM Grandparents AS Child1
INNER JOIN Grandparents AS Child2 ON (Child1.GrandChildID !=
Child2.GrandChildID AND Child1.GrandParentID = Child2.GrandParentID)
WHERE
Child2.GrandChildID NOT IN (SELECT SiblingID FROM Siblings WHERE
Siblings.ChildID = Child1.ChildID);
Best Regards,
Phil Willoughby
--
Managing Director, StrawberryCat Limited
StrawberryCat Limited is registered in England and Wales with Company No.
7234809.
The registered office address of StrawberryCat Limited is:
107 Morgan Le Fay Drive
Eastleigh
SO53 4JH
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
