Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Boxersoft
Starting Member
28 Posts |
 Posted - 2009-09-14 : 08:47:33
|
| I want to store animal pedigree data in SQL Server but I'm not sure how best to structure it. I read the Rob Volk's article on storing lineage, and the Joe Celko articles referenced in it, but both involve single 'parent' nodes. I haven't managed to get my head around how to use either technique when each node has two parents. Can anyone point me at any good articles on doing this sort of stuff? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-14 : 11:13:41
|
| are you by any chance using sql 2008? |
 |
|
|
Boxersoft
Starting Member
28 Posts |
Posted - 2009-09-14 : 11:44:36
|
| Yep - but only the Express version. Ideally I'd like something that uses generic SQL (if there is such a thing in practice) so that I could later move it onto whatever my ISP provides (currently MySQL). It's just for personal use for now though, so proprietary stuff would be OK. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
Boxersoft
Starting Member
28 Posts |
Posted - 2009-09-14 : 13:53:41
|
| Thanks, will do. |
|
|
|
Boxersoft
Starting Member
28 Posts |
Posted - 2009-09-14 : 14:34:12
|
| I've scanned the article, and perhaps I need to read it again more carefully. My initial thoughts are that, although interesting, it doesn't seem to obviously address the problem of dual parents. If I simply use an Adjacency model with two parents (sireID, damID) then I can walk the ancestor tree to a given depth using explicit joins, or multiple database hits by a client application. Neither is ideal. Rob's Lineage approach seemed like a great solution but if you apply it to Sires and Dams separately then instead of getting everyone in the triangle of ancestors you only get those representing the edges of the triangle, i.e. you get fathers, grandfathers etc. and mothers, grandmothers etc. but never mothers of fathers, fathers of mothers and so on.I looked at the possibility of a separate Mating entity that could represent a single Parent node, but I couldn't see a way to make that work because that doesn't give a direct parent/child relationship (the child of a Mating is a Person, not another Mating). |
|
|
|
|
|
|
|
|
Pedigree