I have a business case I have not been able to solve yet. Actually I'm stuck with it lot.
I have huge organization with pretty vertical hierarchy. As average people have 2 team members in their team and there is around 100+ levels from root to bottom (growing constantly). Yet, some members have more than 2 teams and also many have only one team.
I can collect data in 2 steps: 1. When user logs in I get their login information and only then I know he/she exists. 2. As bosses can see their team and their team-team-...-team members salaries and pays I only collect information who is your boss (parent) from each user (authorizing that boss to see your salary and pays). The users don't sometimes know who their direct boss is :) and they see other users in alphabetical order so no sorting by position (at this point).
So giving all that I get random updates to one table with ties between user and one of the users boss. It can but doesn't always (mostly) have to be direct boss of the user.
I can get pretty late updates to very high position in the hierarchy in frequent bases. It is OK to update TREE periodically not on the fly every time I have collected piece of information regarding bosses.
I have given this a thought for 3 days and nights in the row and cannot figure out ideal and not very expensive (for DB) result yet. I'm sure you have good solution for that problem.
At this point I have MySQL but I'm willing to change.