Excellent read on hierachy trees in SQL!
After my post yesterday regarding recursion or temp tables, Frans Bouma posted an excellent read on doing hierachy trees in SQL by representing it as a basic binary tree.
This is exactly what I needed for handling our organizational structure at work. I'm in the process of rewriting the application in J2EE and I'm taking the liberty to restructure a couple of the tables that have been problematic. One of them is the organization table. A lot of queries are run against it, and most of them are pretty ugly. Examples:
1) Often, you need to know the structure of the area the person is in. Sometimes the “root” is above them, sometimes it is where they are. This procedure takes their org area, traverses up until it finds the top level of that section (marked by a flag), then traverses back down. We have a spare table setup with the user's ID and rows for the organizations in their area. A lot of redunancy. It is refreshed every time they log in. Since the organization can only go so deep, it is populated using a series of 6 insert/selects (even if it doesn't need to go that deep).
2) As work goes through the approval process, it needs to constantly check if something is ready to go to the next level when something is approved. Something could go to 3 teams, one is done, one just finished, and one hasn't finished. It can't go to the next level until the 3rd one is approved. This gets ugly as things get higher. It creates a temporary tables, does its 6 insert/selects to get all the areas below the level it is supposed to go to, and it checks to see if these areas were assigned to, if they have completed the work, and if it has all been approved.
Using the technique mentioned in Joe's post, I could easily take the first one from about 8-9 queries and a spare (somewhat bloated) table to 1 select query with a join. The second one, I could easily eliminated the creating of the temporary table and cut nearly a dozen queries. It would be useful in countless other areas as well. Definitely what I needed... and my post wasn't even about work related issues!