Message-ID: <3CEE818B.9020702@wildbrain.com>
Date: Fri, 24 May 2002 11:08:11 -0700
From: Gregory Brauer <greg@wildbrain.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0rc2) Gecko/20020510
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Trees in SQL
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit


I hope this isn't an overly broad topic that ends up diverging into graph
theory, but I have a tree structure of identical items (analogous to a
filesystem directory tree) that I need to store in Postgres.  The
"obvious" design is to give the table that will represent these objects
a field identifying its "parent" that is a relation to the same table.
However, this seems to make many common SQL queries rather difficult.

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?

I'd appreciate any insights anyone can give.

Thanks.

Greg Brauer
greg@wildbrain.com