How to design a database with a table that needs to reference itself?

Question

I'm building a database and have run into a problem that I can't seem to wrap my mind around. The database is much more complex than what is pictured, but the problem can be distilled into the table structure below.

The issue is that every employee has a manager and every manager is an employee. It would seem that these tables have to reference eachother. However, this doesn't seem to work correctly when I set this up.

I'm using cakephp. What is the name of this relationship type? Is this the wrong design? What is the proper design for this situation? I would like it to work as automagically as possible.

Answer 1

Just scrap employees.manager_id. Every manager will have a pointer to a employee row, but not every employee will point to a manager. This would create a traditional one-to-one relationship.

Answer 2

If I understand the question correctly, you don't need the Managers table at all. Since a manager is an employee, there is no reason the managers shouldn't be stored in the Employees table. Simply add a field to the Employees table called manager_id, which for any given row references that employee's manager (which is another row from the same table).

This is called a recursive one-to-one relationship, and it is quite common. In your case, it will also prevent you from having to define duplicate fields across the two entities (names, passwords, job titles, etc).