Storing tree structures

What is the best way to store tree structures in Oracle?

A non-cyclical, single-parent tree. The kind that Oracle's CONNECT BY clause navigates.

In its simplest form, I could simply have one row per node with the current node and its parent node. The root node will have a NULL parent node.

create table t1 (curr number, prev number);

insert into t1 values (1,null);
insert into t1 values (2,1);
insert into t1 values (3,1);

select .... from t1
start with prev is null connect by prev=prior curr

would suffice for navigating this tree.

Now, say a node is moved from one place in the tree to another, a node is deleted, etc. I want to capture all these changes and not simply store the most current tree. Store different versions of the tree.

i.e. How can I *re-create* the tree as an "as of" date?

Also, if I have a CONNECT BY in a query, I cannot have a JOIN in the same query! Seems like a bummer!

Can the new features in Oracle 8/8i help here? Nested tables, object types, collection unnesting, etc? Or will good ol' 7.3.4 suffice?

I hope I am expressing myself clearly enough for someone to help me.

Thanks,
Vikas

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 17 1999 - 12:01:21 CDT