[thelist] [DB Design] Recursive Directory Structure

[rudy]

> 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

the thing is, if you want retrieval logic that can go N levels deep, this
typically results in logic that calls the database inside a loop -- and the
performace is terrible

alternatively -- and this is the choice i frequently recommend -- you decide
to go down some fixed number of levels, say 4, which you do with a 4-way
self join, which is only one call to the database, very efficient, but it
doesn't necessarily traverse the subtree all the way down

the reason i almost always recommend the latter is because i firmly believe
usability trumps programming -- the coder in you is just *itching* to write
the damn code just once and be done with it (meaning, it has to be
recursive), but the designer in you says "whoa, wait a minute, the user
isn't going to want to explode the entire subtree all the way down, just a
few levels is sufficient, and if the user wwants to go deeper, pick another
node further down and expand that"

now here's the question of the moment, feel free to play along at home --

you know the little plus sign you get in the windows explorer folder pane?
and how you have to click on the little plus sign to get it to explode? and
it only explodes one level, right?  did you know you can do that, expand
that folder to show the folders within it, by pressing the plus sign (either
above the equals sign, or on your numeric keypad), or, alternatively, using
the right and left arrow keys? do you know what the keyboard shortcut is to
explode *all* folders to *all* levels?


rudy