just to throw in my two bits: I have done a lot of work with trees in SQL, and IMHO, the best method BY FAR is the one described in the link below (mysql article), mainly due to its capability to handle siblings and descendants. for example, the self-join, parent_node method described elsewhere in this list is failr ok for simple requirements, but is completely useles in the following cases:
select all_descendans_on_any depth for a particular node find out if a node "belongs" to a parent which is not its immediate parent. in other words, SET operations are quite difficult in the node-parent relation, but very easy and efficient in the adjacent list model. I personally stopped looking for a better solution once I came across and comprehended the power of this method... > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of > Sebastian Bermudez > Sent: Wednesday, October 14, 2009 5:10 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] how to represent a tree in SQL > > > > look this: > > http://dev.mysql.com/tech-resources/articles/hierarchical-data.html > > is for mysql but aplies to every sql DB > > > ----- Mensaje original ---- > De: Michael Chen <vancouver.mich...@gmail.com> > Para: sqlite-users@sqlite.org > Enviado: mié, octubre 14, 2009 10:40:45 AM > Asunto: [sqlite] how to represent a tree in SQL > > Dear there, > > I am developing a numerical application, where a single > rooted dynamic tree is the main data structure. I intended to > use SQLite for this purpose and also for other data as well. > However I have no reference how to represent a tree using > tables. I need these functionalities: > (1) basic tree: single root, multi-levels, arbitrary number > of branches, index each node, index each path (from root to a > leaf), lookup parent, lookup descendants > (2) dynamics: delete a path, add a path; maintain parent and > descendants table; maintain history of tree; lookup history > (3) each node has lots of matrix and vectors, which will be > updated with dynamics, and should be tracked > > As you see, it is nontrivial to write a tree structure to > support all these functions, while keep the code clean and > neat. That's why I want to use SQLite to keep things > straight. Is there a good reference on this? > > Michael Chen > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > Yahoo! Cocina > > Encontra las mejores recetas con Yahoo! Cocina. > > > http://ar.mujer.yahoo.com/cocina/ > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
