Subject Re: [firebird-support] Open to suggestions: building a tree
Author Frank Ingermann
Hi Brandon,

Brandon Smith wrote:
> I'm using FB and here's what I want to do:
> Table dir_tree has three fields, Dir_ID (integer, pk), Parent
> (integer, fk to Dir_ID), and This_Dir, varchar(250).
> For the insert proc, I want to bring in a string like
> "d:\top\next\xyz\abc\here is a dir"
> I want this to end up being 6 records in this table, "d", "top",
> "next", "xyz", "abc" and "here is a dir" with the parent field
> pointing to the parent, except for "d" which would have a null parent.
> Standard linked list kind of thing.

Since Helen answered your main question, and the subject says you're
open to suggestions, here's one: ;-)

Have you considered using Nested Sets instead of the parent link
method to store the tree? Whether this makes sense for you or
not depends on the structure (depth) and the amount of inserts/
deletes you'll typically have, but the plain advantage is that
you can do a single, "flat" select to read out the entire tree
at once - whereas the parent link approach requires a recursive
stored proc.

In case you're interested, google for "nested sets" and you'll
find lots of how-tos. I've made a little Firebird/IBO demo
too, if you like i can send it over.

(@Helen: this may be nice for the Contributed section of IBO?)