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.