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 thisenter image description here

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.

  • I hope my commenting this doesn't turn this into an XY problem, but I thought of creating a trigger, BEFORE INSERT ON employees, that would check the manager_id,department_role_id of the employee against all the others, and if there's the same department_role_id but different manager_id, throw exception. This approach seems a bit tacky, however... – Mike Warren Nov 29 '17 at 3:17
  • To clarify: an employee has a manager (another employee) and a department_role. Their department is determined by their department_role. Now, you seem to indicate that everyone with department_role "X" must have the same manager. If that's true, then manager_id should be in department_role, not in employee, because department_role would determine who someone's manager is. – RDFozz Nov 29 '17 at 20:29
  • Also - If employee contains everyone in the company, and everyone is linked to their boss (up to the company CEO/president/whatever), then either everyone is in the same department, or some people must have a boss from a different department. (e.g.: CIO is top manager of IT department; CFO is top manager of finance dept.; CEO is manager of both CIO and CFO). – RDFozz Nov 29 '17 at 20:33
  • Wouldn't moving employees.manager_id to department_role and having the employees reference that be a circular reference, though? – Mike Warren Nov 29 '17 at 22:42
  • Logically, no more circular than employees to itself; however, you may well be right that Oracle might not let you do this. I was addressing the logical aspect first, and checking to be sure this is what you wanted. It seemed a little odd; my organization is relatively large, and there can be enough people doing the same basic task that more than one manager in a given department, dealing with people performing the same roles, is possible. If this is what you want, then determining how to make it work (break the normalization?) would be the next step. – RDFozz Nov 30 '17 at 0:20

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 & 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:

    insert into emloyee (<column_list>) values (<value_list>);
    insert into department_role (<column_list>) values (<value_list>);

    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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.