Sql – Database design for a recursive relationship

data modelingdatabasedatabase-designerdsql

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

First Solution

  • 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)
Second solution

  • 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?

Best Answer

I'd probably go with something like:

enter image description here

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:

enter image description here

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

enter image description here

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

Related Topic