[thelist] [DB Design] Recursive Directory Structure

[Morgan Kelsey]

paul,

> [CAUTION: long post ahead]
>
> Morgan Kelsey wrote:
> > i too, have no idea how to populate celko's left/right nested set model.
> > that i will readily admit.
> > in fact, i've yet to meet anyone who can convincingly (i'm sure they're
> > out there, but they don't hang out where i do).
>
> Ahem.
>
> I think I can populate them fairly convincingly, but I'm not sure
> that I can *explain* it well.
>
[snip]

wow, that's a really great explanation. i'm not going to commit to saying i get it
without playing with it though...hehe

anyway, damn--we trolled for jeff, we uttered his name in vain, and all he did was
swing through and steal rudy's tip? sheesh. can't you get any good free help these
days? :p

unfortunately for all of you, i now feel the urge to attempt to explain the
tree/level method.
the basic table:

CREATE TABLE categories (
    id         numeric(9, 0) IDENTITY (1, 1) NOT NULL
  , name       varchar(100) NOT NULL
  , parent_id  numeric(9, 0) NOT NULL
  , tree_level numeric(9, 0) NOT NULL
  , rank       numeric(9, 0) NOT NULL
)
parent_id tells us the local foreign key that is this record's parent
tree_level is used to determine the level of indentation
rank is used to order the items in the table.
i find this method so simple, it's hard to believe it works.

to get the whole banana:

SELECT name
       , tree_level
    FROM categories
   ORDER BY rank

this outperforms the nested set (lft rgt) model because there is no self-join. the
nested set model is still really fast, but in my own local tests of tables with
2000 entries, tree/rank comes in sub-100MS, while lft/rgt scores around 400MS.

to find the children of a node, assuming the node whose children we want is coming
in as a URL variable (in CF-speak):

SELECT
    id
  , name
FROM categories
WHERE parent_id = #Val(url.parent_id)#

jeff has lots more of the basic INSERT and UPDATE statements worked out for this,
which i hear rumor is being wrapped up into an article....[cough]


nagrom
http://morgankelsey.com/