Any tips of how to handle hierarchical trees in relational model?

Question

I have a tree structure that can be n-levels deep, without restriction. That means that each node can have another n nodes.

What is the best way to retrieve a tree like that without issuing thousands of queries to the database?

I looked at a few other models, like flat table model, Preorder Tree Traversal Algorithm, and so.

Do you guys have any tips or suggestions of how to implement a efficient tree model? My objective in the real end is to have one or two queries that would spit the whole tree for me.

With enough processing i can display the tree in dot net, but that would be in client machine, so, not much of a big deal.

Oh just dropping some more info here:

environment: Oracle or PostgreSQL, C# and Winforms.

Thanks for the attention

Answer 1

There is no efficient tree model.

SQL 2008 - hierarchyid. There is a new data type for handling hiearchies, but it gets large over time.

Or: usual. Parent field in table, pointing to the ID of the parent table.

For queries...

• You can do that a little more optimal with better SQL (one statement PER LEVEL, plus use of a temp table). This is the really tricky part here.
• What we did in a CMS where we had the same was that every node knew its parent, the top node (for multiple graphs) and the next higher container node. Some nodes were marked as containers - they did contain sub-items, but these belonged logically to anothe containe (like: website, with folders, then document with resources like images).

Answer 2

I noticed that you listed your DBs as Oracle or PostgreSQL. If you can stick to Oracle, you can use the start with and connect by commands to easily do what you need. There are plenty of options that will also gracefully handle if there are loops, or if you need to filter out a branch based on some criteria. I've personally used this in a production system that had both heavy reads and writes without any performance issues.

A quick search shows that you can do something similar (although more complex sql wise) with postgreSQL using the with recursive command. I've not personally used this method, so I can't give you any more information then that.