MySQL - Hierarchy Database
Posted: Sun Jul 17, 2011 10:07 am
Here's an interesting problem -- I'm looking to create a site using PHP and MySQL that measures statistics for staff at 5 different levels. Each person in the hierarchy reports to one of the people in the level above.
For example, a staff member would have their own stats and then the Team Manager above them would have the 'roll-up' of stats of all of the staff members below them. The Senior Manager would then have a 'roll-up' of stats of all of the Staff Members in all of their Team Managers.
Structure is as follows:
Director (Top Level)
Site Manager
Senior Manager
Team Manager
Staff Member (Lowest Level)
A further complication is that the structure can change at any time. For example, Julie (a Staff Member) could report to Steve (a Team Manager) from 01/03/2011-04/04/2011. She then moves to Dave's team from 04/04/11 until the present day.
If a query was made for stats on Steve for the whole of 2011 to date, it should only include Julie's stats in the period that she worked on his team.
So how would this be laid out in a MySQL database? Would there be just one table with a 'roleID' and then a 'lineManagerID' field referring to someone else in the same table? Also, how would you query for stats for everyone down the line so that all of their statistics are included (but only when they are part of the relevant hierarchy?)
Any suggestions would be appreciated.
For example, a staff member would have their own stats and then the Team Manager above them would have the 'roll-up' of stats of all of the staff members below them. The Senior Manager would then have a 'roll-up' of stats of all of the Staff Members in all of their Team Managers.
Structure is as follows:
Director (Top Level)
Site Manager
Senior Manager
Team Manager
Staff Member (Lowest Level)
A further complication is that the structure can change at any time. For example, Julie (a Staff Member) could report to Steve (a Team Manager) from 01/03/2011-04/04/2011. She then moves to Dave's team from 04/04/11 until the present day.
If a query was made for stats on Steve for the whole of 2011 to date, it should only include Julie's stats in the period that she worked on his team.
So how would this be laid out in a MySQL database? Would there be just one table with a 'roleID' and then a 'lineManagerID' field referring to someone else in the same table? Also, how would you query for stats for everyone down the line so that all of their statistics are included (but only when they are part of the relevant hierarchy?)
Any suggestions would be appreciated.