Avoiding a possible table reference error I'm working on a project that's due in 2 weeks and have hit a possibly serious roadblock. The app that I'm working on is some tuition reimbursement management system, whose use cases are dependent on the roles of the employee (user) trying to access it. It's a solo project, in which I was given a two-page document of some high-level requirements, involving the employee, the direct supervisor, the department head, and the benefits coordinator. 

After a week of trying to plan it, and work on it, I came up with this: the employees table has a foreign key to itself called manager_id (an employee is a base user, then, iff there are no records such that manager_id equals that employee's employee_id), and a department_roles_id that points to the department_roles table that has a department and a department_role_name. Here's a screenshot of my ERD for this

This approach, however, seems to have a problem. What happens when someone inserts an employee record into employees, with the same department_role_id as another record, but they have two different manager_ids (for example, if one of them was a manager of that department but the other one wasn't)? How to avert this?

NOTE: if it helps any, I'm on Oracle 11g.
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.