Company Employee Management

A hierarchy and a staffing table in one schema: the example built on a recursive association and on an entity that exists only to resolve a many-to-many.

Advanced

The diagram

(0,N)(1,1)(0,1)(0,N)(0,N)(1,1)(0,N)(1,1)EmployeeDepartmentProjectAssignmentWorksInSupervisesStaffedOnOnProjectemployee_nofull_namehired_ondepartment_codenamebudgetproject_codenamestarted_onroleweekly_hours
Sign in to open and edit

Opening an example creates a copy in your own projects. The original example is never modified.

The problem

A company wants a single schema for its staff, its departments and its projects. Every employee has an employee number, a full name and a hiring date, and works in exactly one department; a department has a code, a name and an annual budget, and it is created in the budget before anyone is hired into it. Most employees report to another employee: the company wants to record who reports to whom, and it insists that nobody reports to two managers at the same time and that the chief executive reports to nobody. Employees are also staffed on projects. A project has a code, a name and a start date, and it can exist for months before the first person is assigned to it. A person may work on several projects and a project may involve several people; for each pairing the company needs to know the role the person plays and the number of hours per week. Finally, the reporting line must never close into a loop: nobody may end up, through a chain of managers, reporting to themselves.

Requirements

  • The system must record employees, each with a unique employee number, a full name and a hiring date.
  • The system must record departments, each with a unique department code, a name and an annual budget.
  • Every employee works in exactly one department; a department may have zero, one or many employees.
  • The system must record who reports to whom: an employee has at most one manager, and a manager may have zero, one or many direct reports.
  • The chief executive has no manager; every other employee has exactly one.
  • The system must record projects, each with a unique project code, a name and a start date.
  • The system must record the assignment of an employee to a project, with the role played and the weekly hours.
  • An employee may be assigned to zero, one or many projects; a project may have zero, one or many assignments.
  • The same employee cannot be assigned twice to the same project, and the reporting chain must contain no cycle.

Entities and attributes

Entity Attributes Identifier
Employee employee_no, full_name, hired_on employee_no
Department department_code, name, budget department_code
Project project_code, name, started_on project_code
Assignment role, weekly_hours (employee_no, project_code)

Relations and cardinalities

Relation From To Cardinality (min, max) Note
WorksIn Employee Department (0,N) – (1,1) A department has zero, one or many employees, and every employee belongs to exactly one department. The minimum of 0 is a real state: the department is created in the budget before the first hire.
Supervises Employee Employee (0,1) – (0,N) Recursive: both ends are Employee, and the two ends are two roles. The (0,1) is the subordinate's side — at most one manager, none at the top of the hierarchy; the (0,N) is the manager's side — zero, one or many direct reports.
StaffedOn Employee Assignment (0,N) – (1,1) An employee has zero, one or many assignments, and every assignment belongs to exactly one employee. The (1,1) on the Assignment side makes it dependent: an assignment with no employee is not a record the company can name.
OnProject Assignment Project (0,N) – (1,1) An assignment concerns exactly one project, and a project has zero, one or many assignments. Together with StaffedOn this gives Assignment its identity: the pair (employee, project).

Reading the diagram

  • Four entities, three of which stand alone. Employee, Department and Project each have an identifier the company really uses — employee_no, department_code, project_code — and each can be listed without any other. Assignment is the fourth and it is different in kind: it has no identifier of its own and exists only because an employee works on a project.
  • WorksIn is the ordinary one-to-many. A department has zero, one or many employees; every employee belongs to exactly one department. The maximum of 1 on the Department side is the structural claim — there is no second department to write down, so a single column will hold the fact — and the minimum of 0 is the state the company actually lives in when it opens a department for the coming year.
  • Supervises is the part of the diagram that needs care, because both ends are Employee. One entity plays two roles in the same association: a supervisor, who has reports, and a subordinate, who has a manager. Nothing in the drawing distinguishes the two ends except the cardinalities and, once you write them down, the role names. That is not decoration: without the role names, the sentence 'employee 4471 supervises employee 1180' can be read in both directions, and the two readings mean opposite things. This is the reason the lesson on recursive associations insists on naming the roles before choosing the numbers. The numbers themselves are the rule. On the subordinate side, (0,1): an employee has at most one manager, and the minimum is 0 because exactly one person in the company has none — the chief executive. Writing (1,1) there would be the tempting, tidy choice, and it would make the top of the hierarchy unrecordable, or push the designer into a fictitious row where the chief executive supervises himself. On the supervisor side, (0,N): a manager has zero, one or many direct reports. Zero matters too, because a newly appointed team leader may have no reports yet, and a manager whose team has been reassigned keeps the title and loses the people.
  • What the diagram cannot say is that the hierarchy must not close into a loop. Cardinality counts managers per employee and reports per manager; it constrains the degree of each node, never the shape of the graph. A cycle — 4471 manages 1180, 1180 manages 2033, 2033 manages 4471 — satisfies every pair in the diagram perfectly, and so does a longer one. Only a rule about the transitive closure forbids it, and a rule about the transitive closure has no place in an E-R diagram. It has to be a trigger, a recursive check performed before each update, or a materialised path that is verified when it is written. The direct self-reference 'nobody manages themselves' is the smallest case of the same rule and deserves its own check constraint, because it is the one case a simple constraint can catch.
  • Assignment is the second lesson. Employee and Project are many-to-many: a person works on several projects, a project involves several people. The diagram does not draw a diamond for it, because the pair carries data — role and weekly_hours — and because later facts will need to point at the pairing itself: a timesheet entry, a cost line, a performance note. Both reasons are the same reason seen from two sides. Once the pair has attributes and must be referencable, it needs an identity, and the identity is the pair (employee_no, project_code), borrowed from the two entities it connects. That identifier also delivers a rule the company asked for: the same person cannot be assigned twice to the same project, because the primary key forbids it.
  • Follow the lines to see how the many-to-many works in practice: Employee — StaffedOn — Assignment — OnProject — Project. Reading from an employee gives their assignments, and from each assignment the project; reading from a project gives its assignments, and from each the person. Note that weekly_hours belongs to the assignment and not to the employee: one person may spend twenty hours on one project and ten on another, and the sum across their assignments is a derived value, not a stored one. Note also that role is a plain attribute here; if the company wanted a fixed list of roles with their own properties, Role would become a fifth entity and Assignment would point at it.
  • Department and Project look similar and are deliberately kept apart. Both are organisational units with a code and a name, which is exactly the trap: a generalisation Unit with two subclasses would look tidy and would immediately force a start date on departments and a budget on projects, neither of which the company has. More importantly, the two participate in different facts. An employee belongs to exactly one department — a permanent placement — and is assigned to many projects, across departments, for a period. Merging the two would blur a placement with a temporary allocation, and the first cross-department project would expose the error.
  • Read the diagram as four promises: every employee has one department; every employee has at most one manager, and every manager may have any number of reports; every assignment has one employee and one project; every assignment carries a role and a number of hours. Everything else the company asked for — one chief executive, no cycles, hours per person that stay within a working week — is a rule about sets of rows, and the diagram can only point at it.

From the conceptual model to the logical schema

Department(department_code, name, budget)
  PRIMARY KEY (department_code)
Employee(employee_no, full_name, hired_on, department_code, supervisor_no)
  PRIMARY KEY (employee_no)
  FOREIGN KEY (department_code) REFERENCES Department(department_code)
  FOREIGN KEY (supervisor_no) REFERENCES Employee(employee_no)
Project(project_code, name, started_on)
  PRIMARY KEY (project_code)
Assignment(employee_no, project_code, role, weekly_hours)
  PRIMARY KEY (employee_no, project_code)
  FOREIGN KEY (employee_no) REFERENCES Employee(employee_no)
  FOREIGN KEY (project_code) REFERENCES Project(project_code)