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