Crowsfoot

HR and employee database schema: ER diagram and SQL

HR data is a good place to meet a self-referencing relationship: every employee can have a manager who is also an employee. This schema also keeps salary history in its own table.

Open this schema in the editor Jump to the SQL
departments PK id bigint name varchar employees PK id bigint FK department_id bigint FK manager_id bigint name varchar email varchar hired_on date salaries PK id bigint FK employee_id bigint amount numeric effective_from date leave_requests PK id bigint FK employee_id bigint starts_on date ends_on date status varchar

Tables in this schema

departments

Teams or business units.

employees

Everyone on the payroll. manager_id points back at another employee, and stays empty for the top of the org chart.

salaries

Salary history. Each change is a new row with an effective date.

leave_requests

Time-off requests and their approval status.

How the tables relate

  • employees.department_id references departments.id: one row in departments can have many rows in employees.
  • employees.manager_id references employees.id: one row in employees can have many rows in employees.
  • salaries.employee_id references employees.id: one row in employees can have many rows in salaries.
  • leave_requests.employee_id references employees.id: one row in employees can have many rows in leave_requests.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE departments (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL
);

CREATE TABLE employees (
  id bigint PRIMARY KEY,
  department_id bigint NOT NULL REFERENCES departments (id),
  manager_id bigint REFERENCES employees (id),
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL,
  hired_on date NOT NULL
);

CREATE TABLE salaries (
  id bigint PRIMARY KEY,
  employee_id bigint NOT NULL REFERENCES employees (id),
  amount numeric(12,2) NOT NULL,
  effective_from date NOT NULL
);

CREATE TABLE leave_requests (
  id bigint PRIMARY KEY,
  employee_id bigint NOT NULL REFERENCES employees (id),
  starts_on date NOT NULL,
  ends_on date NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'pending'
);

Design notes

  • manager_id references the same table, which lets you walk the org chart with a recursive query.
  • Keeping salaries in a separate table gives you history and lets you restrict who can read pay.
  • Compensation and personal data are sensitive. Limit access and check the rules that apply where you operate.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.