Database design for a recursive relationship

Question

Consider this case where I'm trying to model a database for a company:

• Entities: Employees, Managers, Departments.
• An Employee works in only 1 Department while a Department may have many Employees working in it.
• A Manager may manage only 1 Department and similarly a Department may have only 1 Manager.
• A Manager supervises many Employees, but an Employee is only supervised by one Manager.

Now I have 2 ways to model this:

First solution:

I'll consider that the Manager entity inherits from the Employee entity considering that I'll keep data that is unique to the Managers (e.g. Bonus & Status).

• Since the relation between Department and Employee is 1:N then I'll put the Department Id as a foreign key in the Employee table for the Works relation.

• Since the relation between Department and Manager is 1:1 then I'll put the Department Id as a foreign key in the Manager table for the Manages relation.

Problem: How can I represent the recursive relation between the Manager and Employee?

---

Second solution:

I'll consider that the Manager entity is not needed as other Employees may also have a Bonus and Status. (Actually I added these 2 attributes just to see how to model it in both cases)

• Since the relation between Department and Employee is 1:N then I'll put the Department Id as a foreign key in the Employee table for the Works relation.
• Since the relation between Employee and Manager is 1:N then I'll put the Employee Id as a foreign key in the Employee table for the Supervises relation and call it Manager Id.

Problem: How can I represent the relation between the Manager and Department?

---

Questions:

1. Is there any obvious mistakes in both design as they are?
2. How to solve each problem in both cases?
3. Is there a better solution than these two?

Solution 1

I'd probably go with something like:

This model has the following characteristics:

• Manager "inherits" employee.
  • To represent an employee, insert a single row in EMPLOYEE.
  • To represent a manager, insert one row in EMPLOYEE and one row in MANAGER.
• A department can have multiple employees.
• Every department has exactly 1 manager and every manager manages 0 or 1 departments.
• A supervisor can be ordinary employee or a manager.
• Departments are not required to "match":
  • A supervisor can work in different department from the supervised employee.
  • A manager can manage different department from where (s)he works.
  • If a supervisor is manager, then the department (s)he manages, the department (s)he works in and the department(s) of his/her supervised employees can all be different.

NOTE: If your DBMS does not support deferred constraints, you'll want to make the DEPARTMENT.MANAGER_ID NULL-able, to break the cycle that would otherwise prevent you from inserting the new data.

---

If the departments are required to match, then you'd either employ a DBMS-specific technique (such as triggers or "special" constraints), or "propagate" the DEPARTMENT_ID into the PK of employees. This propagation is what ultimately enables the matching:

Since EMPLOYEE_ID must be globally unique, it cannot stay in the composite key together with the DEPARTMENT_ID. So, we make it alternate key and instead use the surrogate EMPLOYEE_NO in the PK.

This model prevents you from having a manager that manages one department and works in another, or a supervisor that supervises employees from a different department.

---

In case you are not familiar with the symbol...

...it denotes a "category". In this context, you can simply interpret it as a "1 to 0 or 1" relationship between EMPLOYEE and MANAGER.

Solution 2

Without getting into details, I do assure you that the Employee/Manager/Department solution is, on the long term, a source of displeasure (at first) then a real PITA (later on) for the persons in charge of maintaining the database and/or developping its interface. So I do advise you to stick to your 2nd proposal.

Regarding the manager/department relation, you have mainly two ways to represent this relation. Both solutions authorize you to keep your recursive "Manager manages Employee" relation in addition to a "manager manages Department" relation that you can implement as follows:

1 - first/simple way: add a manager/employee id in your department table. This field is of course a foreign key to the employee table

2 - second/more complex solution: add a "manager" table with the following fields:

Manager id (PK, surrogate)
Department id (FK)
Employee id (FK)
beginningDate
endingDate

where you will store the management history: who, for which department, from when, till when

In this case do not forget to add some logic (trigger, or client-side control) to translate your business rules such as you can have only one manager for a specific period and a specific department, no department can stay more than ... without a manager, etc.

EDIT:

3 - a richer solution would be a generalisation of my second proposal, and will allow you to keep track of everyone's career in the company. You can do it with a 'works in' table, such as this one (as we call it here a 'position' table, I will keep the same terminology here:

Position id (PK, surrogate)
Department id (FK)
Employee id (FK)
Position Level (FK)
beginningDate
endingDate

Where 'position level' leads to another table holding the different positions that can exist in a department, one of them being of course the 'manager' position.

This proposal is closer to what is used in HR database and software, and you might not need such a complex solution. But keep in mind that splitting the human beings in multiple tables is ALWAYS a mistake.

EDIT: following your comment ...

To make things clear, I'd advise you to adjust your field names. I'd propose you to have the following fields:

Tbl_Employee.id_EmployeeManager

and

Tbl_Department.id_DepartmentManager

Doing this, we (or any developper) will immediately understand that id_EmployeeManager participates in the recursive relation between the persons, while id_DepartmentManager participates in the relation between people and department.

Back to your questions, and according to me, you should not create the following link:

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_EmployeeManager

By doing so, you are meaning that somebody cannot be a department manager unless he is already managing employees. What about departments with a single employee? What about people named managers of a department newly created, where still no employee is allocated? It does not work. The right link should be:

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_Employee

You could of course add some business rules saying for example that "an employee managing a department can only be a manager" (id_Employee exists somewhere as id_EmployeeManager) or "an employee managing a department cannot have a manager (where id_EmployeeManager for this employee is null...). But these are only business rules. Your data model is clean to accept all rules as long as the basic rule is respected, which is that a department is managed by an employee!

Comments

[Songo]

The one table to hold them all is like my second solution right? You are basically proposing that the Employee table to have the following structure Employee (Employee Id, Employee Name, Manager Id, Department Id, Manager Department Id) right?

[Diego]

you don't need a Manager Department Id, just department Id because if the employee is a manager, its department will be stored in the departmentId column

[Songo]

The Manager of a Department may not be the Manager of the Employee. The Manager for the Employee is his direct supervisor not the whole department manager. How to solve this?

[Songo]

But the Manager of a Department may not be the Manager of the Employee. The Manager for the Employee is his direct supervisor not the whole department manager. How to solve this?

[Diego]

for the manager of the department you will have a ManagerId field on the department table that will be key to an employee. This employee can or cannot be manager of other employees

[Songo]

ok I see your point :) For the sake of completeness could you please tell me in case I want to use the first solution (Manager extends Employee) how can I do a recursive relation between them? Add a Manager Id column to the base table Employee maybe?

[Diego]

but, thats exactly what I suggested

[Songo]

+1 for the illustrative answer :) But are you sure this part is correct "Since EMPLOYEE_NO must be globally unique, it cannot stay in the composite key together with the DEPARTMENT_ID" ? Did you mean EMPLOYEE_ID perhaps ? and what exactly do you mean by "If the departments are required to match" ?

[Songo]

+1 for the great explanation :) But I have few questions. In your first (and simplest) solution you said that I should add a Manager/Employee Id field to the department table. Since I already added Manager Id to Employee table for the recursive relation then I should add a Manager Id to the Department table referencing the Manager Id in the Employee table right ? This way only a manager can manage a department not any employee I think.

[Branko Dimitrijevic]

@Songo I made a typo (it should be ID not NO), corrected, thanks!

[Branko Dimitrijevic]

@Songo Regarding "matching departments" please re-read the answer. Essentially, this is a matter of (dis)allowing a manager to manage the department (s)he does not also work in and supervisor to supervise employees from a different department from his/her own.

[Songo]

Perfect I was just making sure that I got it right. Another question concerning the 2nd part of you answer, Why did you make DEPARTMENT_ID in the DEPARTMENT table both PK & FK ? If it is a FK what is it referencing?

[Branko Dimitrijevic]

@Songo There is a FOREIGN KEY: DEPARTMENT {DEPARTMENT_ID, MANAGER_NO} referencing MANAGER {DEPARTMENT_ID, MANAGER_NO}.

[Branko Dimitrijevic]

@Songo BTW, my apologies for another typo: in the second diagram, there should be MANAGER.DEPARTMENT_ID (not DEPARTMENT_NO).

[Songo]

OK I'm officially lost here :D If my database was totally empty how should I fill it with data to follow your schema? If it was me I would create an empty Department first, but since DEPARTMENT_ID is referencing the MANAGER table then I'll have to add a Manager first, but since the Manager Id is referencing the EMPLOYEE table then I should create an Employee first, but to create an Employee I have to assign a value for his DEPARTMENT_ID which is referencing the DEPARTMENT table... and I'm back to square one feeling dizzy :S

[Branko Dimitrijevic]

@Songo You are absolutely right - there is a circular reference here (in both diagrams). To allow the initial insert, either use deferred constraints or (for the first diagram) allow NULL in DEPARTMENT.MANAGER_ID (as already mentioned in my answer). Allowing NULL is probably not an option in the second diagram, since the part of the FK's child endpoint is in PK. If you really want the second diagram and you use DBMS without support for deferred constraints, you'll probably be forced to use a database-specific mechanism such as triggers to enforce integrity.

[Songo]

Actually this is the first time I ever heard of Deferred Constraints mainly because I'm not a database developer and most of my work is on MySql which I just found out that it doesn't support deferred constraints :( Anyway thanks for the excellent explanation I wish I could up vote your answer again :D

[pascalvgemert]

If I understand it well, then I guess by giving the Employee a relation to an employee who is a manager. So the employee table gets a field ManagerID which relate to a EmployeeID.