Re: Trees in SQL

Greg,

> What sort of strategies are best for storing tree structures in a
> relational database, and how would one structure SQL queries to find,
> say, "all of the children anywhere under this node", or to represent
> the condition "if this node is a child at any depth under this other
> node"?  Are there good strategies for preventing cycles?

Joe Celko covers this extensively in his book "SQL for Smarties."  You should
definitely buy it and read the two chapters on tree structures before going
any further.

I will be writing an article for Techdocs within the month on implementing
Celko's "Linear Nested Sets Model" of trees in PostgreSQL.  If you can't
wait, buy the book, e-mail me, and I'll send you my notes.

--
-Josh Berkus