Re: Self-referential records

On Sunday 24. January 2010 16.22.00 Wayne E. Pfeffer wrote:
> If you do not use null to represent a root node, when you go to unwind the
> data from the table to generate a hierarchy tree, you could end up with an
> infinite loop. The query will always be looking for the next parent in the
> hierarchy. Meaning, you will want to find the parent of a node using the
> given parent_id, the query will find the parent of 1 to be 1, then it will
> look again for the parent of 1 it will find 1, etc. etc. ad nauseum. I enjoy
> using recursion as much as the next guy, but this could cause some serious
> issues with the PostgreSQL query engine eating up system resources.

It doesn't really matter if the root node is NULL or 0 or whatever. You just
have to realize that the root node is a special case and program accordingly.
An adjacency tree is not a normalized structure, and will never be. It's the
programmer's responsibility to ensure that circular references can't occur.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/