0

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.

laving
  • 49
  • 6
  • Welcome to Stack Overflow! You might want to check out [how to ask a question](http://stackoverflow.com/help/how-to-ask). Formatting your question correctly will go a long way to getting you the answer you are looking for. In this case, posting any code that you have already tried would be helpful. – Gary Storey Jun 10 '15 at 20:30
  • Thanks! Every code written so far wont solve the problem. I'm looking for new solution. – laving Jun 10 '15 at 20:54
  • You lost me at the 'as bosses can see their team-team-...team members salaries' sorry. So, a boss has a team that team is made up of 1 or many people. Each of those people can also be a boss with a team, and that the root boss can see? – LordBaconPants Jun 10 '15 at 20:58
  • Yes. You got it right. It is a regular tree only I can add people to the tree by asking them who is your boss. Answer won't always be who is my drect boss but rather who is any of my bosses. Most of the times I get many answers - my bosses are: X, Y and Z. – laving Jun 10 '15 at 21:01
  • Posting the code that didn't work shows that you actually TRIED to do this yourself. It might also prevent someone from posting code that you already tried. – Gary Storey Jun 10 '15 at 21:03
  • Is it possible for me to be the boss of my bosses boss? And your ultimate output is to pass in userID, and bring back everyone below her in the chain? – LordBaconPants Jun 10 '15 at 21:09
  • What I think you need here is 2 tables: Users and User_Relationship. The User_RelationShip table would have a UserID and BossUserID, and as many rows per userID you need to have all the bosses. TO query it though I imagine you need some recursion going. I'm not very experienced with it myself, hence this not being an answer, or with MySQL. You can do it in SQLServer in versions 2008+ I believe. http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query might be a good place to start through – LordBaconPants Jun 10 '15 at 21:24

0 Answers0