HR and Payroll ER Diagram

An HR and payroll schema connects the organization chart to the money: employees sit in departments under managers, job titles define positions, and payroll runs produce one payslip per employee per period. Attendance and leave requests feed the inputs that payroll eventually consumes.

employees is the center of the schema. It points at departments and job_titles for organizational context, and at itself through manager_id, which draws the reporting line as a self-referencing edge. That self-reference is nullable because exactly one person has no manager; everyone else resolves upward through the chain. email is UNIQUE, hired_on carries the start date, and status allows a row to remain after someone leaves without deleting the history that payslips depend on. Keeping the employee row is what lets a former employee's payroll records stay attributable, and that distinction matters at audit time.

The payroll side is a two-level chain. payroll_runs represents a period with period_start, period_end and a status, and UNIQUE (period_start, period_end) prevents accidentally running the same period twice. payslips references both the run and the employee; its UNIQUE (payroll_run_id, employee_id) is the rule that makes the relationship one payslip per person per run, while gross_pay, deductions and net_pay keep the numbers on the record. Attendance is keyed by (employee_id, work_date), one row per person per day, and leave_requests records absences that payroll may need to account for. Keeping periods and slips separate also makes corrections traceable.

What breaks in practice is re-running payroll. Because payslips are immutable financial records in most organizations, corrections should be a new run or an adjustment line, not an update that erases what was paid. The schema allows either, so the process must choose; the UNIQUE pair stops duplicate slips within a run but not duplicate runs for the same period, which only the period constraint covers. Deleting an employee cascades to attendance and leave_requests but is blocked by payslips, which is the desired asymmetry: time data is operational, pay data is historical.

A salary_history table (employee_id, effective_from, base_salary) is the first realistic addition, since a single salary column cannot explain a raise or reconstruct an old payslip. payroll_adjustments (payslip_id, type, amount, reason) handles bonuses, deductions and corrections without overloading the payslip columns. If leave balances matter, leave_requests should reference leave_types with an annual allowance, and an employee_positions history table records transfers between departments and job titles that employees would otherwise overwrite in place. A benefits table is the other common request once deductions include employer contributions and eligibility rules rather than one total.

The diagram

Tables (7)

Relationships (7)

• 1:Ndepartments.id→employees.department_id
• 1:Njob_titles.id→employees.job_title_id
• 1:Nemployees.id→employees.manager_id
• 1:Npayroll_runs.id→payslips.payroll_run_idON DELETE CASCADE
• 1:Nemployees.id→payslips.employee_id
• 1:Nemployees.id→leave_requests.employee_idON DELETE CASCADE
• 1:Nemployees.id→attendance.employee_idON DELETE CASCADE

The SQL

CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE job_titles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NOT NULL UNIQUE,
  min_salary DECIMAL(12,2)
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  department_id INTEGER REFERENCES departments(id),
  job_title_id INTEGER REFERENCES job_titles(id),
  manager_id INTEGER REFERENCES employees(id),
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  hired_on DATE NOT NULL DEFAULT CURRENT_DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

CREATE TABLE payroll_runs (
  id SERIAL PRIMARY KEY,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  UNIQUE (period_start, period_end),
  CHECK (period_end >= period_start)
);

CREATE TABLE payslips (
  id SERIAL PRIMARY KEY,
  payroll_run_id INTEGER NOT NULL REFERENCES payroll_runs(id) ON DELETE CASCADE,
  employee_id INTEGER NOT NULL REFERENCES employees(id),
  gross_pay DECIMAL(12,2) NOT NULL CHECK (gross_pay >= 0),
  deductions DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (deductions >= 0),
  net_pay DECIMAL(12,2) NOT NULL CHECK (net_pay >= 0),
  UNIQUE (payroll_run_id, employee_id)
);

CREATE TABLE leave_requests (
  id SERIAL PRIMARY KEY,
  employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  leave_type VARCHAR(40) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  CHECK (ends_on >= starts_on)
);

CREATE TABLE attendance (
  id SERIAL PRIMARY KEY,
  employee_id INTEGER NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  work_date DATE NOT NULL,
  hours_worked DECIMAL(5,2) NOT NULL CHECK (hours_worked >= 0 AND hours_worked <= 24),
  UNIQUE (employee_id, work_date)
);

Keep learning

• What Is an ER Diagram?
• How to Create an ER Diagram
• SQL to ER Diagram: The Complete Guide
• Primary Key vs Foreign Key
• All guides