0

I have a MySQL database which contains 2 (relevant) tables: Staff, Supervisors.

Staff contains: StaffID, Name, CostCentre

and Supervisors contains: InstanceID, StaffID, SupervisorID, Obsolete

The Staff table contains all staff in the company, and the Supervisors table links one StaffID (StaffID) to another StaffID (SupervisorID). If we create a new relationship (i.e. staff A no longer reports to staff B, but instead to staff C), then we create a new record and set the original 'obsolete' flag to 'true'.

I have arranged it this way because the Supervisor:Subordinate relationships are subject to change, and are not guaranteed to be correct: We want to be able to change them and maintain an audit trail.

What I need to do is to get a recursive list of CostCentres.

What that means is, say I start with some cost centre. I get a list of all staff who are in that cost centre, and generate a list of all their subordinates. Then I get a list of all those cost centres and repeat for each of those.

I already have a number of procedures, two of which may be relevant:

CALL getCostCentre(iCostCentre) /* get a list of all staff in iCostCentre) */
CALL getSupervisees(iSupervisor) /* get a list of all staff who report to iSupervisor */
Ozzah
  • 10,631
  • 16
  • 77
  • 116

1 Answers1

0

I would create a single table containing staff and supervisors, let's call it Employee, and another table for holding relationships (each table with a delete flag field and a change date field for audit trial). The table relationship has a subordinate and a master. And a table for cost centers holding references to the employees. There are some open source CRMs around that you can download (use it maybe) and eventually learn how to structure your db. Regarding cost centers, you can learn from the table Group in standard CRMs

perissf
  • 15,979
  • 14
  • 80
  • 117
  • Isn't this exactly how I described my DB? I have a Staff table containing all employees and a Supervisors table which connects one or more employees to another employee who is their manager. I'm not interested in CRMs: my application is highly specific. – Ozzah Aug 10 '11 at 06:35
  • well, in effect there is only one difference, although important: it is having one table instead of 2 for the employees. Anyway, I still can't understand why you are talking about recursive procedure. Let's take supervisor A who has subordinates B and D, each with its cost center. Is it possible that B and D are supervisors for other subordinates? In case yes, there is the need for a recursive procedure, in case no, there isn't. – perissf Aug 10 '11 at 07:08
  • No, I think you are very confused. You said "_I would create a single table containing staff and supervisors, let's call it Employee, and another table for holding relationships_". This is _EXACTLY_ what I have done. In answer to your question: Of course B and D are also supervisors: Only when you get to the very bottom of the tree do you find employees who have no subordinates. I am quite certain that my problem is a recursive problem. – Ozzah Aug 10 '11 at 07:14
  • Okay, I get it. Sorry for my confusion. Have you looked at this example? http://stackoverflow.com/questions/3438111/mysql-stored-procedure-that-calles-itself-recursively – perissf Aug 10 '11 at 07:17