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.
MySQL - Hierarchy Database
Moderator: General Moderators
Re: MySQL - Hierarchy Database
You will need 3 "main" tables and some "cross reference" tables:
Users table - here you put all info regarding every person in the company.
Roles table - here you put all info regarding roles themselves.
Team table - here you put the team name (optional) and the team leader ID (from the users table) (1:1 relationship)
TeamUsers table - here you put "user membership" in the form of TeamId-UserId pairs (N:M relationship)
RoleUsers table - here you put "user position" in the form of RoleId-UserId pairs (N:M relationship)
That's the DB deisgn and it should be enough to store all information needed by the bussines logic.
Users table - here you put all info regarding every person in the company.
Roles table - here you put all info regarding roles themselves.
Team table - here you put the team name (optional) and the team leader ID (from the users table) (1:1 relationship)
TeamUsers table - here you put "user membership" in the form of TeamId-UserId pairs (N:M relationship)
RoleUsers table - here you put "user position" in the form of RoleId-UserId pairs (N:M relationship)
That's the DB deisgn and it should be enough to store all information needed by the bussines logic.
You can add an additional numeric field to Roles table - "position"- higher number means higher position.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?)
There are 10 types of people in this world, those who understand binary and those who don't
Re: MySQL - Hierarchy Database
The M:N RoleUsers table is to be used if and if only a member may have 2 or more positions in the firsm (i.e. Temaleader and Staff Member). If not just add an additional field into the Users table - RoleId (1:n relationship)
There are 10 types of people in this world, those who understand binary and those who don't