Chris,
> Suppose you want to use an RDBMS to store messages for a threaded
> message forum like usenet and then display the messages. A toy table
> definition (that I've tried to make standards compliant) might look
> like:
This is not at all a new problem. Tree sturctures in SQL are one of those
"classic" problems with many solutions.
Joe Celko's "SQL for Smarties, 2nd Edition" has 2 chapters on tree structures.
The first thing you should do is read these chapters; otherwise, you won't be
able to make an informed decision about what tree structure to use. The main
ones are:
Relational-table (a table for each level of the tree)
Adjacency List (what you described)
String-Append (tree in a Text field, as USA:California:SanFrancisco)
Nested Set (hard to explain)
In a few months, Joe will be publishing a whole book about them, and Joe and I
will have an article in the first issue of DotDot covering implementation of
a nested set tree using PostgreSQL "data-push" functions. (I would not
recommend nested sets for your issue, as the tree does not update quickly,
and works poorly for fragmented trees)
Right now, you can also check out two PostgreSQL-proprietary tree structure
solutions in the /contrib directory of your Postgres source (assuming that
you have 7.3 or up):
Joe Conway's connectby() in /dblink, which works similar
to Oracles' CONNECT BY
/ltree , which uses the "string-building" tree stucture technique.
--
Josh Berkus
Aglio Database Solutions
San Francisco