Human resources (HR) data model

This example models the core of an HR system: employees and who they report to, the organization (departments, jobs, positions and work locations), each employee's history of positions and salaries, time off and performance reviews.

#Design notes

• The reporting line is a self-relationship. employee.manager_id points at another employee; the Reporting Line hierarchy turns it into an org chart.
• Jobs and positions are different things. A job is a definition with a pay grade ("Software Engineer II"); a position is a budgeted seat in a department for a job, and it can be vacant.
• History is kept, not overwritten. employment_history records each position held, and salary has effective dates, with exactly one open row per employee.
• Sensitive data is flagged as PII: names, contact details, date of birth, salary and review comments, so it can be masked or access-controlled.
• Leave requests record who approved them, a second relationship to employee.

To extend it: add benefit_enrollment, payroll_run and payslip, training_course and certification, or job_application for recruiting.

#Diagram

Human Resources (HR): 9 entities, 3 reference lists and 14 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Department

An organizational unit, such as Finance or Engineering.

#Employee

A person employed by the company. Managers are employees too.

#Employment History

Each position an employee has held, for promotions and transfers.

#Job

A job definition with a pay grade, such as Software Engineer II.

#Leave Request

A request for time off, approved by the employee's manager.

#Performance Review

A periodic review of an employee by a reviewer.

#Position

A budgeted seat in a department for a job. A position can be vacant.

#Salary

Base salary over time. Exactly one row per employee has no end date.

#Work Location

An office or site where employees work.

#Country

Countries, identified by their ISO 3166-1 alpha-2 code.

#Employment Type

Kinds of employment contract.

#Leave Type

Kinds of time off.

#Relationships

#Reference data

#Country

6 values · Source: ISO 3166-1 (excerpt)

#Employment Type

4 values · Source: Example values

#Leave Type

4 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- Human Resources (HR) — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Employment Type
CREATE TABLE employment_type (
  type_code TEXT NOT NULL,
  type_name TEXT NOT NULL,
  PRIMARY KEY (type_code)
);
COMMENT ON TABLE employment_type IS 'Kinds of employment contract.';

-- Reference list: Leave Type
CREATE TABLE leave_type (
  type_code TEXT NOT NULL,
  type_name TEXT NOT NULL,
  is_paid BOOLEAN NOT NULL,
  PRIMARY KEY (type_code)
);
COMMENT ON TABLE leave_type IS 'Kinds of time off.';

-- Reference list: Country
CREATE TABLE country (
  country_code TEXT NOT NULL,
  country_name TEXT NOT NULL,
  PRIMARY KEY (country_code)
);
COMMENT ON TABLE country IS 'Countries, identified by their ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_code IS 'ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_name IS 'English short name.';

CREATE TABLE employee (
  employee_id INTEGER NOT NULL,
  employee_number TEXT NOT NULL,
  full_name TEXT NOT NULL,
  work_email TEXT NOT NULL,
  date_of_birth DATE,
  hire_date DATE NOT NULL,
  termination_date DATE,
  department_id INTEGER NOT NULL,
  position_id INTEGER NOT NULL,
  manager_id INTEGER,
  employment_type_code TEXT NOT NULL,
  work_location_id INTEGER NOT NULL,
  PRIMARY KEY (employee_id)
);
COMMENT ON TABLE employee IS 'A person employed by the company. Managers are employees too.';
COMMENT ON COLUMN employee.employee_number IS 'Number on the badge and payslip.';
COMMENT ON COLUMN employee.termination_date IS 'Empty while employed.';
COMMENT ON COLUMN employee.manager_id IS 'Direct manager; empty for the CEO.';

CREATE TABLE department (
  department_id INTEGER NOT NULL,
  department_name TEXT NOT NULL,
  cost_center TEXT NOT NULL,
  head_employee_id INTEGER,
  PRIMARY KEY (department_id)
);
COMMENT ON TABLE department IS 'An organizational unit, such as Finance or Engineering.';
COMMENT ON COLUMN department.head_employee_id IS 'The department head.';

CREATE TABLE job (
  job_id INTEGER NOT NULL,
  job_title TEXT NOT NULL,
  job_family TEXT,
  pay_grade TEXT NOT NULL,
  min_salary NUMERIC(12,2),
  max_salary NUMERIC(12,2),
  PRIMARY KEY (job_id)
);
COMMENT ON TABLE job IS 'A job definition with a pay grade, such as Software Engineer II.';
COMMENT ON COLUMN job.min_salary IS 'Salary band minimum.';
COMMENT ON COLUMN job.max_salary IS 'Salary band maximum.';

CREATE TABLE position (
  position_id INTEGER NOT NULL,
  position_code TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  job_id INTEGER NOT NULL,
  is_vacant BOOLEAN NOT NULL,
  PRIMARY KEY (position_id)
);
COMMENT ON TABLE position IS 'A budgeted seat in a department for a job. A position can be vacant.';

CREATE TABLE work_location (
  work_location_id INTEGER NOT NULL,
  location_name TEXT NOT NULL,
  city TEXT NOT NULL,
  country_code TEXT NOT NULL,
  PRIMARY KEY (work_location_id)
);
COMMENT ON TABLE work_location IS 'An office or site where employees work.';

CREATE TABLE employment_history (
  history_id BIGINT NOT NULL,
  employee_id INTEGER NOT NULL,
  position_id INTEGER NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  change_reason TEXT,
  PRIMARY KEY (history_id)
);
COMMENT ON TABLE employment_history IS 'Each position an employee has held, for promotions and transfers.';
COMMENT ON COLUMN employment_history.end_date IS 'Empty for the current position.';

CREATE TABLE salary (
  salary_id BIGINT NOT NULL,
  employee_id INTEGER NOT NULL,
  annual_amount NUMERIC(12,2) NOT NULL,
  currency_code TEXT NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE,
  PRIMARY KEY (salary_id)
);
COMMENT ON TABLE salary IS 'Base salary over time. Exactly one row per employee has no end date.';
COMMENT ON COLUMN salary.currency_code IS 'ISO 4217 code.';

CREATE TABLE leave_request (
  leave_request_id BIGINT NOT NULL,
  employee_id INTEGER NOT NULL,
  leave_type_code TEXT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status TEXT NOT NULL,
  approver_id INTEGER,
  PRIMARY KEY (leave_request_id)
);
COMMENT ON TABLE leave_request IS 'A request for time off, approved by the employee''s manager.';
COMMENT ON COLUMN leave_request.status IS 'Requested, approved, rejected or cancelled.';
COMMENT ON COLUMN leave_request.approver_id IS 'The manager who decided.';

CREATE TABLE performance_review (
  review_id BIGINT NOT NULL,
  employee_id INTEGER NOT NULL,
  reviewer_id INTEGER NOT NULL,
  review_period TEXT NOT NULL,
  rating INTEGER,
  comments TEXT,
  PRIMARY KEY (review_id)
);
COMMENT ON TABLE performance_review IS 'A periodic review of an employee by a reviewer.';
COMMENT ON COLUMN performance_review.rating IS '1 (below) to 5 (exceptional).';

-- Keys between tables
ALTER TABLE employee ADD CONSTRAINT uq_employee_employee_number UNIQUE (employee_number);
ALTER TABLE employee ADD CONSTRAINT uq_employee_work_email UNIQUE (work_email);
ALTER TABLE position ADD CONSTRAINT uq_position_position_code UNIQUE (position_code);
ALTER TABLE employee ADD CONSTRAINT fk_employee_employment_type_code FOREIGN KEY (employment_type_code) REFERENCES employment_type (type_code);
ALTER TABLE work_location ADD CONSTRAINT fk_work_location_country_code FOREIGN KEY (country_code) REFERENCES country (country_code);
ALTER TABLE leave_request ADD CONSTRAINT fk_leave_request_leave_type_code FOREIGN KEY (leave_type_code) REFERENCES leave_type (type_code);
ALTER TABLE employee ADD CONSTRAINT fk_reports_to FOREIGN KEY (manager_id) REFERENCES employee (employee_id);
ALTER TABLE employee ADD CONSTRAINT fk_member_of FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE employee ADD CONSTRAINT fk_holds FOREIGN KEY (position_id) REFERENCES position (position_id);
ALTER TABLE employee ADD CONSTRAINT fk_works_at FOREIGN KEY (work_location_id) REFERENCES work_location (work_location_id);
ALTER TABLE department ADD CONSTRAINT fk_headed_by FOREIGN KEY (head_employee_id) REFERENCES employee (employee_id);
ALTER TABLE position ADD CONSTRAINT fk_position_in FOREIGN KEY (department_id) REFERENCES department (department_id);
ALTER TABLE position ADD CONSTRAINT fk_position_of_job FOREIGN KEY (job_id) REFERENCES job (job_id);
ALTER TABLE employment_history ADD CONSTRAINT fk_history_of FOREIGN KEY (employee_id) REFERENCES employee (employee_id);
ALTER TABLE employment_history ADD CONSTRAINT fk_history_position FOREIGN KEY (position_id) REFERENCES position (position_id);
ALTER TABLE salary ADD CONSTRAINT fk_paid_to FOREIGN KEY (employee_id) REFERENCES employee (employee_id);
ALTER TABLE leave_request ADD CONSTRAINT fk_requested_by FOREIGN KEY (employee_id) REFERENCES employee (employee_id);
ALTER TABLE leave_request ADD CONSTRAINT fk_approved_by FOREIGN KEY (approver_id) REFERENCES employee (employee_id);
ALTER TABLE performance_review ADD CONSTRAINT fk_review_of FOREIGN KEY (employee_id) REFERENCES employee (employee_id);
ALTER TABLE performance_review ADD CONSTRAINT fk_reviewed_by FOREIGN KEY (reviewer_id) REFERENCES employee (employee_id);

-- Reference values
INSERT INTO employment_type (type_code, type_name) VALUES
  ('FT', 'Full-time'),
  ('PT', 'Part-time'),
  ('CON', 'Contractor'),
  ('INT', 'Intern');

INSERT INTO leave_type (type_code, type_name, is_paid) VALUES
  ('VAC', 'Vacation', TRUE),
  ('SICK', 'Sick leave', TRUE),
  ('PARENT', 'Parental leave', TRUE),
  ('UNPAID', 'Unpaid leave', FALSE);

INSERT INTO country (country_code, country_name) VALUES
  ('US', 'United States'),
  ('CA', 'Canada'),
  ('GB', 'United Kingdom'),
  ('DE', 'Germany'),
  ('FR', 'France'),
  ('AU', 'Australia');

-- Hierarchies (navigation over the relationships above):
--   Reporting Line: root employee, 2 level(s)

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: Human Resources (HR)
---
%% Made with EntityModel.com
erDiagram
  employee {
    int employee_id PK "Employee ID"
    string employee_number "Number on the badge and payslip."
    string full_name "Full Name"
    string work_email "Work Email"
    date date_of_birth "Date Of Birth"
    date hire_date "Hire Date"
    date termination_date "Empty while employed."
    int department_id FK "Department ID"
    int position_id FK "Position ID"
    int manager_id FK "Direct manager; empty for the CEO."
    string employment_type_code FK "Employment Type Code"
    int work_location_id FK "Work Location ID"
  }
  department {
    int department_id PK "Department ID"
    string department_name "Department Name"
    string cost_center "Cost Center"
    int head_employee_id FK "The department head."
  }
  job {
    int job_id PK "Job ID"
    string job_title "Job Title"
    string job_family "Job Family"
    string pay_grade "Pay Grade"
    decimal min_salary "Salary band minimum."
    decimal max_salary "Salary band maximum."
  }
  position {
    int position_id PK "Position ID"
    string position_code "Position Code"
    int department_id FK "Department ID"
    int job_id FK "Job ID"
    boolean is_vacant "Is Vacant"
  }
  work_location {
    int work_location_id PK "Work Location ID"
    string location_name "Location Name"
    string city "City"
    string country_code FK "Country Code"
  }
  employment_history {
    bigint history_id PK "History ID"
    int employee_id FK "Employee ID"
    int position_id FK "Position ID"
    date start_date "Start Date"
    date end_date "Empty for the current position."
    string change_reason "Change Reason"
  }
  salary {
    bigint salary_id PK "Salary ID"
    int employee_id FK "Employee ID"
    decimal annual_amount "Annual Amount"
    string currency_code "ISO 4217 code."
    date effective_from "Effective From"
    date effective_to "Effective To"
  }
  leave_request {
    bigint leave_request_id PK "Leave Request ID"
    int employee_id FK "Employee ID"
    string leave_type_code FK "Leave Type Code"
    date start_date "Start Date"
    date end_date "End Date"
    string status "Requested, approved, rejected or cancelled."
    int approver_id FK "The manager who decided."
  }
  performance_review {
    bigint review_id PK "Review ID"
    int employee_id FK "Employee ID"
    int reviewer_id FK "Reviewer ID"
    string review_period "Review Period"
    int rating "1 (below) to 5 (exceptional)."
    string comments "Comments"
  }
  employment_type {
    string type_code PK "Type Code"
    string type_name "Type Name"
  }
  leave_type {
    string type_code PK "Type Code"
    string type_name "Type Name"
    boolean is_paid "Is Paid"
  }
  country {
    string country_code PK "ISO 3166-1 alpha-2 code."
    string country_name "English short name."
  }
  employee }o--|| employee : "Reports To"
  employee }o--|| department : "Member Of"
  employee }o--|| position : "Holds"
  employee }o--|| work_location : "Works At"
  department }o--|| employee : "Headed By"
  position }o--|| department : "In"
  position }o--|| job : "Of Job"
  employment_history }o--|| employee : "History Of"
  employment_history }o--|| position : "In Position"
  salary }o--|| employee : "Paid To"
  leave_request }o--|| employee : "Requested By"
  leave_request }o--|| employee : "Approved By"
  performance_review }o--|| employee : "Review Of"
  performance_review }o--|| employee : "Reviewed By"
  employee }o..|| employment_type : "Employment Type Code"
  work_location }o..|| country : "Country Code"
  leave_request }o..|| leave_type : "Leave Type Code"

#Frequently asked questions

#How do you model a manager–employee relationship in a database?

Add a manager_id column on the employee table that references the same table's employee_id. Each employee has at most one direct manager, and walking the relationship upward gives the reporting chain.

#What is the difference between a job and a position in HR data?

A job describes the work and its pay band, and many people can share it. A position is one budgeted seat for a job in a department; it is filled by one employee or is vacant.