ERD - How to add an attribute to one side of the recursive relation?

Question

The simplified scenario for the ERD

• A company has employees (each has unique id emp_id)
• Each employee is supervised by another employee
• A supervising employee has a special attribute SID

Here is my ERD for the scenario and the the employee table. But the problem is every single employee who aren't supervisors will have an unnecessary column SID with NULL. I feel like there should be another table with emp_id and his/her SID to solve this. In order to do that how do I modify the ERD?

Answer 1

For a simple case like this I would just leave the column as-is with the value NULL for non-supervisors.

For more complicated entities, look into single table inheritance which follows a pattern like:

Entity             SubType1
===========        ================
Id     (PK) <----  Id       (PK,FK)
SharedProp1   |    ST1SpecificProp1
SharedProp2   |    ST1SpecificProp2
SharedProp3   |    ...
...           |    
              |    SubType2
              |    ================
              `--  Id       (PK,FK)
                   ST2SpecificProp1
                   ...

All the common properties go in the base table, properties specific to a subtype (in your example the main type is person, the subtypes are supervisor and other).

There may also be a discriminator property in the shared table which states which subtype the current record it.

Many ORMs use this model to manage sub-types. Some DB systems have built-in support (IIRC postgres does via an optional module), but it is not difficult to put together yourself.

Answer 2

You create a supervisor entity. It has the same identification as the employee. Each supervisor is an employee, an employee can be a supervisor. In the data model, the supervisors' ID is a foreign key to the corresponding employee.