Something to investigate is to use an AVL tree structure with rowids as the pointers. It would stay balanced and you could present family trees quite simply as well as use SQL to extract data on individuals and sets of individuals.
Jay A. Kreibich wrote: > On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall: > >> Hi, >> >> I am planning a database for animal breeding. I need to store the >> relations between individuals and therefore I have to build something >> like a tree structure. But of course with two parents (There wont be >> cloned animals in the database .-) afaik) >> > > > > >> - adjacency list (not very difficult to understand) >> > > Also easy to work with two parents, just have a "father" column and a > "mother" column. > > Adjacency lists are quick to update, but many queries can't be done > in (standard) SQL by itself. That usually isn't a problem, and it > doesn't sound like you'll have millions of records. > > >> - nested sets (hm, more difficult) >> > > Nested sets only work with one parent. They're also extremely > expensive to update, although you can do some interesting tricks with > queries. > > >> - b tree (to difficult) >> > > B-trees are more of a way to sort and access large amount of linear > data. They're not really designed to represent data that is > inherently tree like. > > >> - ? (something I missed?) >> > > You might want to see if you can find a copy of one of Joe Celko's > books... either "SQL for Smarties" (which has a chapter on trees) or > the book "Joe Celko's Trees and Hierarchies in SQL for Smarties" > which is all about trees. > > That said, it sounds like you'd be well serviced by a two parent > adjacency list and a bit of custom programming/scripting. > > -j > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
