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?

Answer 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.

Answer 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!