On 24 Feb 2011, at 1:06pm, Josh Marell wrote:
> I would say that couple should be a 3 column table, with coupleID,
> partner1ID, partner2ID. It looks like right now, you have just coupleID and
> partnerID, that doubles the number of rows you have.
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
You need to make a convention so that '1, 2, parent' means that 1 is the parent
of 2, and not the other way around. However, '1, 3, spouse' is a bidirectional
relationship. But if you allow same-sex marriages then you can't use a
convention that id1 of a spouse is always male.
Using the above schema with the appropriate JOINs gives you things like
SELECT id1.name FROM Relationship AS r, Person AS p1, Person as p2
WHERE r.connection = 'grandparent' AND p1.sex = 'female'
will find all grandmothers' names. I'll leave 'cousins' as an exercise but
I'll comment that you'll need more than p1 and p2 for it.
Oh and while you're there, don't try to do things like 'firstname, surname'.
People don't really work like that.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
