Multi Tiered system with Nested Sets

[sectionb]

Hi guys, I am designing a database & php site that needs to add things up, and it needs to include a lot of groups and subgroups..

 

The easiest way to explain it would be an example of a graph to show how well a department in a business is doing (this isn't actually what it's for but it's probably the easiest description).

 

Example:

Each operator does different amount of work - some is measured in 10's others in 1000's etc.. So an indexation system is required to bring everything into line. This is done in PHP already but it's probably a good point for the sake of understanding the problem..

Let's say there are 5 departments in the company, some have sub-departments, and sub-departments have sub-departments etc etc.. The number of sub-departments will always increase and could be 10, 50 or 100+.

Under each department or sub department, there are staff who's performance is measured on a scale of 1 to 100.

Depending on where the staff member works depends on how much impact they have on the whole business. To calculate this, each department has a "weighting amount" which defines it's impact on the calculation for the whole business. The weighting amount is a percentage.

 

Therefore I have 3 tables

1) Staff table - this includes their name, description, work hours etc etc

2) Performance data - this is recorded every month, and is a figure between 1 and 100

3) Departments - this contains the name of the department, the weight of the department and the "parent" of the department.

 

Each staff member has a StaffID which is common between the Staff table and the Performance table.

 

I need to run a query using MySQL / PHP which will allow me to take 1 month of data from every employee in the company and incorperate each staff members' department's weighting into the equation.

So a staff member's performance needs to be added to their sibling's performance, and calculation against the weighting of their department. As their department will be a child node of it's parent department, the calculation needs to continue until it gets to the top of the tree, at which time a single indexed & weighted figure is calculated to show the performance of all of the staff in the departments as 1 single figure.

 

Obviously this could be done with a whole ton of queries following each other, but I'm wondering if there's a "best practise" way of doing such a thing, and if MySQL is the best way of storing the data for this type of setup?

 

Everything I've seen so far for Nested Tree's doesn't seem to show me how I could get both the parent and the weight from each row, and then use them in a calculation - either in PHP or MySQL.

 

I hope that makes sense!?

Any help  / advice on the design & structure would be much appreciated.

 

Cheers,

Anthony