[thelist] [DB Design] Recursive Directory Structure

Joshua Olson joshua at waetech.com
Mon Feb 3 10:16:08 CST 2003


----- Original Message -----
From: "rudy" <r937 at interlog.com>
Sent: Saturday, February 01, 2003 8:52 PM


> > Am I explaining this clearly?
>
> yup, but you didn't ask a question    ;o)
>
> create table category
>  ( categoryid integer not null primary key
>  , categoryname varchar(50) not null
>  , categoryparent integer null
>  )
>
> unless this is for an oracle database, you are going to have to make some
> type of compromise when it comes to pulling out the hierarchy for display
> purposes (also called traversing or exploding the tree) -- given a node
> (categoryid), you want to expand the subtree beneath that node

I'm coming in a little late, but I want to point out an alternative
structure that has worked quite well for me in the past.  The structure is
similar to the one Rudy mentioned, but it adds two additional fields that
speed up the queries for fields.

One field indicates the depth of the node from the root (the indent level)
and the other indicates the position of the node within the fully expanded
tree within a display that places a single node on a single row.

The principal downside to this tree is that insertion, deletion, and
movement of nodes requires a little work (though less than some other
techniques I've implemented)

Here is a link for more information:

<wrap>
http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/404
7/index.html
</wrap>

The article describes four methods for recursive trees, but the model I'm
referring to is called "Flat Table Model"

GL
-joshua




More information about the thelist mailing list