how to enforce a database self join (or is there a better way)

Question

I have an employees table in a database, and I want to link an employee to their manager:

Employee Table

• employee_id
• first_name
• last_name
• manager_id

If the manager_id is just another row in the same table where the manager has it as their employee, what is the best way to enforce that if I delete an employee it verifies that this employee is not the manager of another employee?

Is there a better best practice for this?

Answer 1

You can use a foreign key constraint (without cascading delete). If you try to delete a manager that still has other employees under him, the database will detect that automatically and the operation will fail.

Answer 2

You can use a self join just like you join to other tables using PK & FK.

I think you should design this with some things to consider. Examples:

• Different Managers: Tom is a Jr. Pgmr today, and Jerry is his Manager today. Tomorrow Tom may report to Sebastian.
• Promotions: Tom is a Jr. Pgmr today, and Jerry is his Manager today. Tomorrow Tom may become a manager himself and have people reporting to him.
• Promotions 2: Tom may go from Jr Pgmr to Sr Pgmr to Team Lead to Project Lead to Manager. Do you want to save this?
• Reporting: You may want to show hierarchy at a point in time (some user will come ask for this for a report sooner or later)

You may want to consider splitting up the data into separate entities - normalizing the data as follows (or even more as your needs may be)

• Employees Table: Basic employee info
• Position Lookup Table: List of all positions in the organization
• Employee Position Table: Track with start date and end date when a position change occurred for the employee.
• Employee Position Hierarchy Table: What Employee Position reports to another Employee Position (using a double join instead of a self join); with start and end timestamps

Answer 3

Use the foreign key, here is a "usual" approach to this:

Answer 4

Use a many-to-many association to cover your bases, should your application (or organization) evolve.

Employee.Employee_id  <- EmployeeManager.Employee_id 
                         EmployeeManager.Manager_id -> Employee.Employee_id
                         Manager_role_type
                         etc.

Always use foreign key constraints.