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: [email protected]
[mailto:[email protected]] On Behalf Of Michael Chen
Sent: Wednesday, October 14, 2009 9:41 AM
To: [email protected]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
