Personally I'd put the nodes and vertices/paths/edge between them in separate tables. (unless you're happy with the 1 vertex between nodes constraint)
It does really get horrible if you're planning to use SQL to find relations with a distance apart of > 1 vertex. I have an oh so vague recollection (not specific to sqlite) of someone using a huge number of boolean columns and some incredible monster of a 'case' statement to push tree parsing off the client app and back onto the database server. The details completely escape me now and it probably introduced lots of constraints on tree depth or number of nodes / vertices.....oh for a perfect memory eh. People a lot cleverer than me have done a pile of work on this. http://blog.monstuff.com/archives/000026.html http://www.sqlteam.com/article/more-trees-hierarchies-in-sql ..ooops, I appear to have stayed off topic. Apologies. -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hoover, Jeffrey Sent: Wednesday, October 14, 2009 2:51 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] how to represent a tree in SQL Isn't most of the complexity in the software? Representing a tree is fairly simple, it just requires a foreign key in the table referencing the primary key of the same table... Create table tree_node ( node_id integer primary key, distance_from_root integer not null, -- 0 implies root node parent_node integer, -- FK to node_id, column should be index ...data columns... ); Create view tree as Select * from tree_node where distance_from_root=0; All kinds of denormalizations and elaborations possible from here... -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Michael Chen Sent: Wednesday, October 14, 2009 9:41 AM To: sqlite-users@sqlite.org Subject: [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 _______________________________________________ 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
