Carl Meyer wrote:
>hi,
>
>say i have a table with something like
>
>id,parent,description
>
>
>
Depending on what your most common types of queries are, you might be
better off with Joe Celko's nested set model (from the book SQL for
Smarties). It would do:
id, left, right, description
where you traverse the tree depth-first and as you pass the left side of
each node you increment a counter (left) and as you pass by it again on
the right you increment it again (right).
This model provides much easier ways of making queries such as "give me
all descendants of id=2" because there's then no recursion (WHERE
child.left between parent.left and parent.right).
I had a table set up exactly as you have described, and when the
recursion proved too costly in terms of db performance, I went to nested
set and we're pleased with the results.
Just another option,
Fran