Trees in SQL

From: Gregory Brauer <greg(at)wildbrain(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Trees in SQL
Date: 2002-05-24 18:08:11
Message-ID: 3CEE818B.9020702@wildbrain.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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(at)wildbrain(dot)com

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Gregory Brauer 2002-05-24 18:34:41 Re: Trees in SQL
Previous Message Doug Fields 2002-05-24 17:20:43 Efficiency question: VARCHAR with empty string vs NULL