Thanks all! It will take me a while to learn the materials in your posts. One thing I care most is DYNAMICS of a tree. I am not sure it is covered in the mentioned references before I study them. Thanks all again!
On Wed, Oct 14, 2009 at 9:18 AM, O'Neill, Owen <oone...@averyberkel.com>wrote: > > 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 > -- Best regards, Michael Chen Google Voice Phone.: 847-448-0647 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
