On Wed, 14 Oct 2009, Michael Chen wrote: > 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, I've not yet had a need to address trees and hierarchies in SQL, but I will unconditionally recommend you run to borrow or buy a copy of Joe Celko's "TREES & HIERARCHIES IN SQL", (Morgan-Kaufmann), 2004 ISBN 1-55860-920-2. I read his dbms columns in the 1980s and 1990s, read and use his "SQL Programming Style" and "SQL for Smarties," and communicated with him for advice on time-and-date based applications. If you still have questions after reading this book, send him an e-mail message. Rich -- Richard B. Shepard, Ph.D. | Integrity Credibility Applied Ecosystem Services, Inc. | Innovation <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users