Avoiding a possible table reference error
Based on the comments, it sounds like every department_role will belong to one and only one manager (but a single manager could manage multiple department roles).

If this is the case, then the database design should enforce it; department_role should include the column manager_id, a foreign key to the employee table.

And, if that's true, and every employee with a manager has a department_role as well, then including manager_id in employee would be denormalizing the database; you'd have to keep the employee.manager_id in sync with the department_role.manager_id. Since you could identify the manager based on the department_role_id, manager_id shouldn't be in employee.

You brought up the concern that this would be an illegal reference in Oracle. I located a StackOverflow Q &amp; A that addressed this.

Their solution is:


Create the table without the constraint first
Optionally, "prime the pump" by manually inserting managers and database roles before adding the constraints.
Create the constraints next (before inserting any data), using ALTER TABLE ... ADD CONSTRAINT ....
If at some point you find that you can't add a manager because a department role doesn't exist, and you can't add the role because the manager doesn't exist, you can use the following to get around it:

ALTER SESSION SET CONSTRAINTS = DEFERRED;
insert into emloyee (&lt;column_list&gt;) values (&lt;value_list&gt;);
insert into department_role (&lt;column_list&gt;) values (&lt;value_list&gt;);
commit;


That said, given your set-up, I don't think you'll need to (unless someone is explicitly given a department_role that they manage, meaning they manage themselves, which seems unlikely).


NOTE: Logically, the top person in the organization will not have a manager, and may or may not have a department role, so your constraint must allow for that (either an employee with no department_role_id, or a department_role with no manager_id). For example, either the company CEO has no department_role listed, or the department_role of "CEO" would have no manager listed.