DiagramDB
DiagramDB / Employee Management System ERD

ER Diagram for Employee Management System

An HR database: employees in departments, a manager hierarchy, job titles with salary history, projects staffed by several people, and leave requests. The manager relationship is a classic self-referencing foreign key.

All examples
Drawing 7 tables…

Why each relationship is drawn this way

  • employee → managermanager_id references employees itself. It is nullable because someone sits at the top of the hierarchy.
  • employee → salariesSalary history as dated rows (to_date NULL = current) instead of overwriting a salary column.
  • employees ↔ projectsproject_assignments resolves the many-to-many and holds the role and hours on each project.
  • leave_requests → employees (twice)Two foreign keys to the same table: who asked, and who approved (nullable while pending).

The schema

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  location VARCHAR(80)
);
CREATE TABLE employees (
  id INT PRIMARY KEY,
  department_id INT NOT NULL REFERENCES departments(id),
  manager_id INT REFERENCES employees(id),
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  hired_on DATE NOT NULL
);
CREATE TABLE jobs (
  id INT PRIMARY KEY,
  title VARCHAR(80) NOT NULL UNIQUE,
  min_salary INT,
  max_salary INT
);
CREATE TABLE salaries (
  id INT PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(id),
  job_id INT NOT NULL REFERENCES jobs(id),
  annual_amount INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE
);
CREATE TABLE projects (
  id INT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  starts_on DATE,
  ends_on DATE
);
CREATE TABLE project_assignments (
  employee_id INT NOT NULL,
  project_id INT NOT NULL,
  role VARCHAR(40),
  hours_per_week INT,
  PRIMARY KEY (employee_id, project_id),
  FOREIGN KEY (employee_id) REFERENCES employees(id),
  FOREIGN KEY (project_id) REFERENCES projects(id)
);
CREATE TABLE leave_requests (
  id INT PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(id),
  approved_by INT REFERENCES employees(id),
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  status VARCHAR(20) NOT NULL
);

Design notes

  • Walk the reporting line with a recursive CTE: WITH RECURSIVE chain AS (...).
  • Name the second foreign key by its role (approved_by), not employee_id2.

Tables at a glance

  • departments: id, name, location
  • employees: id, department_id, manager_id, full_name, email, hired_on
  • jobs: id, title, min_salary, max_salary
  • salaries: id, employee_id, job_id, annual_amount, from_date, to_date
  • projects: id, name, starts_on, ends_on
  • project_assignments: employee_id, project_id, role, hours_per_week
  • leave_requests: id, employee_id, approved_by, starts_on, ends_on, status

Questions

What are the entities in a employee management system ER diagram?

This design uses 7 tables: departments, employees, jobs, salaries, projects, project_assignments, leave_requests. manager_id references employees itself. It is nullable because someone sits at the top of the hierarchy.

How many relationships does the employee management system ER diagram have?

8 foreign-key relationships. The many-to-many ones are resolved with junction tables, which is how they must be stored in a relational database.

Can I edit this ER diagram and export it?

Yes. “Edit in the ER diagram generator” opens the same SQL in the editor, where you can add tables, drag boxes and export SVG, PNG, Mermaid, DBML or SQL for PostgreSQL, MySQL, SQL Server or SQLite.

Which notation is used?

Crow’s foot: a fork means many, a bar means exactly one and a circle means optional (zero). A nullable foreign key gets the circle on the parent end.

More ER diagram examples