# Human resources (HR) data model

> An example human resources data model with ER diagram — employees and reporting lines, departments, jobs and positions, employment history, salaries, leave requests and performance reviews — with a data dictionary and SQL DDL.

Source: https://entitymodel.com/docs/examples/human-resources/

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.

<!-- diagram -->

**[Open this model in EntityModel.com](https://entitymodel.com/#/share/v1.xRvbbts49lcI7cNOATkjOdd6X9Z1nMTTNgkcdxa7i0KgJdpmK5EakkrqGfTfF7xIFik5kV2ni-nDmKTO_RyeC_OXt6Asg8IbeIgILNYZTVCKmOebjegRMY4p8Qah73FasBh5A28lRM4Hv_5a--YoppnnewRm6kCRQRIxpL_gnu8JLFK5cyN3wLTcAb_cTN94vkfZEhL8JxQKlzdWgD-WgAH6BrM8VYASxGOGc3NwSMDNFCgKBgBleUrXCHHwhMUKQMBQTpnAZAlSTJAPEpRDJjJEBPfBFzrnAJIE5JRjCY77BoI8AFaYC8rWPuAwhQwj7oMUwUcEGPqjQFyYbxFTgiKx3HjE6Ikfeb5XSc0LjwLP92AhVpQ1GfN8jwsoCu4NPJjnjD6ixPO9mCEoUBIpxfSD_lkvDHvB8SwIBurfURAE__F8r8iTTueUnjDi3uC_f5UqKoUlRYp5nsJ1ZHbGmx2t4Eisc7nBBSQJZIpCmlIWxTSR6387Hp8NL66aypHi4ZSUiknAfA3ECoGYZjkk6yPwERK4RIwDyFBNfYJSKUUoBMPzQrQTHmFJSArnKK0RDSaX0to0wZPbmed7mEc5wxlk6-grWnsDwQrke1KNmKGk_G1MTKosOOl73_0mQlJkc-UbDaS35Y5B_DCbTm6vvSaWguA_ClT-ssWlgQBKlIzmMFkibWNwzVOcS4lsiBz3ApfORZGmWocbCq-KNAW3es2lDfPIHGuXSI5xUzgfIcMQXFPyJ0zRn3X0T5R9jVAGcVrD_y_KvoKxWdxNOK3oM4n-aGnQ_xPGGToqd2u0SLeI6CKaYyZWNXIuoUDgbgHemXVD0eVwNva2YAzfXlz0gtNeP6wjWGGGIomlBvwGMwQu9ZoNeLut9YN-2Av6vcCCLhDLMFHB0EUy22y147JtapzlYg2eVjit_Cs5siRVhUTbny6r9RaP2s7PcVAHXkZWG_S9Wd0RsCWhTAcOG7CJJi1wbaFcYoZiAQyMf0jBiDVYUKYcbzS-s10tDGzcm0tCBUYdBd2goCQ3W-cIjPS2oWc6vhpPx7ejcTS-nU1m_1ZMLxBDRN6sf5mI28Qi78hC5IWIqqgoDaXC__054V3NGo6a0hg2VaPc9YPZ2kk_fe_7Z3mZsSLWBMuY_dn3IENQ6hxR10UrA2veQJf1vY530PnwdHTRdIAhAfXcAqagIFj4gBfxCkAOrjBRlzdlYEyWmCDEMFluv3328pddbyDbi2ooneheQ7pnjK9dKBvu68hjykUUIyKsi29EuQCjcvWluL5BMhr1joPA4m6FYBK13-k3CCbgmYvd1vRshWoJHpBw2_z4OSu9q2ehNRq_0HnTSH9Tix2tM7w6Oz47a8mQvtA5SNACEx0STeKawzVYMpigjaE-0IV4kmlSqSgwmWw30y90bsvyNzo_RGrUd8QS6bzexjMza_sbYxuzLuYFzHC6dlBflYsu7hcNPYfrSIm8flPBNbg2a92N_PrcuqkwiVQJUSf1IybgoVws7-_xaPJx-EHmIQzF2FRdfd_jMZRw-67xaABgLnPEDBOcFZlt8G9PA1UD2BfntxZy4LcDkwO_NckJjyt69nHCMploeuL9ZqejO74dngzDoMUd50WyRAIlgCMoACYA1mOKTBGgdNkjMKzKRhBDAuYIPMIYErHdJXdOhna_NcLW5MvJTyqMdmLS2UvtLL3mV7fXvYdxvxec_7T8smOU6xrPMI-0EmsQJxz8Xq4ZoO_u7j6Mh7fPAl7AlKP97NxKz5rGbqVo3S3-ZHhyedpvTY8WC6wTII4FAk8rZNXikpztRv0DueSu5m2pqkLpZEQVvh_Oh4YFF7iUjpURyfy8lgrpn92vBw3YzrEKItjaddSRXt63gDBQ2wsHC-WztcOnh_3MuFa_mFbatk6TCgI31ZmOBt2_Or08P24WvDBebUIzrHpPCKwgByuUJr4K4zmjGVVdP9VjEQwSvkCMbzd1w4WToupFy7zfTa73s_DT0yBs7zx1bXV1b2q9XmnOBWTCbVs8yMX2hkV3l-wH_eNecO40SxBJXGxjknRvjpR1f1wwJi2xlIzVJolXkCxRxBDkKupWPqrWwbRc35523pcGt2uhXGVrjvNUCVtHhzkdnV-9bQrhHeRI97jXgD4iBgTO0BEYf4OxSNeAEgQYfZKdXNuVCAWIJEBKfrvPaLi2jZk88SAec3ER9Ft6M6_iMZCQAqYRzGTorAEfqnUwLNe75897NWDD8KKZ1WvbjZtXiFnfluu5GG3TmDzcgZN-eA4kVDuX__RwaYl9sUCxwI8oWjA11agkX26AK72xQ2P0rBeErq9XeARtxTKjDo5nnW1EsxwR3ri71KAnMoOepud9UHOgabXd0QGHYf8qvGgpOgwiHYlwhmTS4YNyHlQOTUrT_jsvO5fb_c5iwPYFi_jDeOFJEJyf_yQv1Jy19V01Yz_Sct3A_qFu6-_D0U-7D896Yb_X7-97H74M-9i92-Ww0uJDL-wWWYz5oWRj5j5g6AuKZe1NmaypY5SmyGnhDe_vp3e_j63YYwA4s4ChWe3UNjT-BJ5WFCQoxomL9-XW4cz4rZVjbYbDkR4Ot_QuagPkaXnmAGNXTBMcm5E0oAsrE56v1Wxcbj0XRPQJW6yaxMOEjfMgDH5S1CiZbWOm1UaeA21fSUZKWuRNSd2X6_tXosoVb2ykUL5qqGMrF56x9BD8MkcpfXoDBAWn4Bf0LUa5Hoq8sc39xK5OM_ViwqpMqyWXrSpn2THLbY67tteKM33A9pJNSG-0Psbno4tRQxzvMUm4dI3ao4-YEsFg_EwXr-3mablz6mreySnsUZ3C5rQ4FLYfbm_IhwE9mWxsCWuVOKNHmBZGBhvmFaF-jT4Lom8fvXeP3kMm2o-O7m6dsyOjEsoah7WR1w9P5ERKV1guA1H1emmshQAMY63Jxbak78CmV2Z7_z97c5KV1zM42UV182xJLLRC8oSDe72yQ7NV_t7XjCX_tg1VhPoVfSUKx_4eJqP3zscPOP6q34m9_Pn9cDpuWPA9lE0ImHYF8un2flhdXyWQT0R-0gJi05bexz02HUXHN0bVxg86hgakHtvhRMJa4KoAwgzIgvQ4PDvrhQCm-Qr2-lVp2u49O3VWd_ShZq28hTS7l9ps-zruVhK3p8c5LS6yTDFfAb6iTACJyaGIYJlxyzRe6rq7D1mSVZz5NkMN2H7jo9Gw-dEIEpjAttPX77aieI_JMqFZ21eX4-ZX10gm3Ou241fT5vErJrPzttPDT83Tsr3PYIrhC25WMxiVibFcvDGfpCr-8BXOnXxcvmbluvVRwam94xSQLZGw12LIEkxgqivc20Gon_Y-QZZEm1RVAdbNE4bk41VUbepHVTLVm-NEPZxSGWO7ucliyn50OwBIduSt1iEUIJMPSGRvMbHeYjU8-bOiFuEl0a5Yf_hVn2oj-XQyoouXJGO9Ouoim48KMrhbtIjmBnKg97tJ50Xm7GFp_ZEMTRP-Em-1AXkXzm4MzAZXKE3Au3U3loak3pdqttBf4rg-hnBmoFw-a36BZXdW2oVvOZfkYChaWH_A6nnogbTZmI06r55QEs3XdQ4t49zXmW8UZK3Apmph0tGTX-Su8W6rdbhE6vzVDHR_h5yQLZ5YewXyqr5YMUcXkX4Y9iyD-kgXzu4WQL8p-wncmZcT1otmPd5si6HOFHdv0zTD0q3BdDMEPgSPW4yzZLSmrI7s7hhgJ6SutdYwOzmUPrfEUZn2O_lCNU7cV4uyPmtPFcaQkUOFly3KY2Xj2Amd7sRm_1TIwG8PoGb7lZksW-KvxOOwHCy1smh2D8VivTvf7Jrawaa1X76_JlX_tTXWmMbvAf1vq7lqRI4mX4HR7Rar_ijtQGzW--iyVllhxCCLV9idHJgSIJIlQLNfMK1KhA96n1HqcN72Z3Hy7WqCcrHyBmHgezBN6VOUFanAUa7aJt5ANTh8L0WPKNUkqf9V4_a5rONWNqXmT0FMaVU1MGxa6hWZW4dVMpXdFa0Z2QVX5EQao2RY8vf9s_xP98CNdAk1gv7-Pw)** to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or [download model.json](/docs/examples/human-resources/model.json).

## Entities

| Entity | Type | Description | Primary Key | Display Attribute | Attributes |
| --- | --- | --- | --- | --- | --- |
| Department | Entity | An organizational unit, such as Finance or Engineering. | department_id | department_name | 4 |
| Employee | Entity | A person employed by the company. Managers are employees too. | employee_id | full_name | 12 |
| Employment History | Entity | Each position an employee has held, for promotions and transfers. | history_id | start_date | 6 |
| Job | Entity | A job definition with a pay grade, such as Software Engineer II. | job_id | job_title | 6 |
| Leave Request | Entity | A request for time off, approved by the employee's manager. | leave_request_id | start_date | 7 |
| Performance Review | Entity | A periodic review of an employee by a reviewer. | review_id | review_period | 6 |
| Position | Entity | A budgeted seat in a department for a job. A position can be vacant. | position_id | position_code | 5 |
| Salary | Entity | Base salary over time. Exactly one row per employee has no end date. | salary_id | annual_amount | 6 |
| Work Location | Entity | An office or site where employees work. | work_location_id | location_name | 4 |
| Country | Reference | Countries, identified by their ISO 3166-1 alpha-2 code. | country_code | country_name | 2 |
| Employment Type | Reference | Kinds of employment contract. | type_code | type_name | 2 |
| Leave Type | Reference | Kinds of time off. | type_code | type_name | 3 |

## Attributes

### Department

An organizational unit, such as Finance or Engineering.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| department_id | INT | PK, Required |  | 30 |  |
| department_name | STRING | Label, Required |  | Engineering |  |
| cost_center | STRING | Required |  | CC-3000 |  |
| head_employee_id | INT |  |  | 1001 | The department head. |

### Employee

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| employee_id | INT | PK, Required |  | 1042 |  |
| employee_number | STRING | Required |  | E-01042 | Number on the badge and payslip. |
| full_name | STRING | Label, Required, PII |  | Maria Gonzalez |  |
| work_email | STRING | Required, PII |  | maria.gonzalez@acme.example |  |
| date_of_birth | DATE | PII |  | 1988-05-21 |  |
| hire_date | DATE | Required |  | 2021-02-01 |  |
| termination_date | DATE |  |  |  | Empty while employed. |
| department_id | INT | Required |  | 30 |  |
| position_id | INT | Required |  | 301 |  |
| manager_id | INT |  |  | 1001 | Direct manager; empty for the CEO. |
| employment_type_code | REFERENCE | Required | → employment_type.type_code | FT |  |
| work_location_id | INT | Required |  | 2 |  |

### Employment History

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| history_id | BIGINT | PK, Required |  | 55012 |  |
| employee_id | INT | Required |  | 1042 |  |
| position_id | INT | Required |  | 301 |  |
| start_date | DATE | Label, Required |  | 2023-07-01 |  |
| end_date | DATE |  |  |  | Empty for the current position. |
| change_reason | STRING |  |  | Promotion |  |

### Job

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| job_id | INT | PK, Required |  | 12 |  |
| job_title | STRING | Label, Required |  | Software Engineer II |  |
| job_family | STRING |  |  | Engineering |  |
| pay_grade | STRING | Required |  | G7 |  |
| min_salary | DECIMAL(12,2) |  |  | 95000.00 | Salary band minimum. |
| max_salary | DECIMAL(12,2) |  |  | 135000.00 | Salary band maximum. |

### Leave Request

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| leave_request_id | BIGINT | PK, Required |  | 40077 |  |
| employee_id | INT | Required |  | 1042 |  |
| leave_type_code | REFERENCE | Required | → leave_type.type_code | VAC |  |
| start_date | DATE | Label, Required |  | 2026-12-22 |  |
| end_date | DATE | Required |  | 2026-12-31 |  |
| status | STRING | Required |  | APPROVED | Requested, approved, rejected or cancelled. |
| approver_id | INT |  |  | 1001 | The manager who decided. |

### Performance Review

A periodic review of an employee by a reviewer.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| review_id | BIGINT | PK, Required |  | 7010 |  |
| employee_id | INT | Required |  | 1042 |  |
| reviewer_id | INT | Required |  | 1001 |  |
| review_period | STRING | Label, Required |  | 2026-H1 |  |
| rating | INT |  |  | 4 | 1 (below) to 5 (exceptional). |
| comments | STRING | PII |  |  |  |

### Position

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| position_id | INT | PK, Required |  | 301 |  |
| position_code | STRING | Label, Required |  | ENG-SE2-07 |  |
| department_id | INT | Required |  | 30 |  |
| job_id | INT | Required |  | 12 |  |
| is_vacant | BOOLEAN | Required |  | false |  |

### Salary

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| salary_id | BIGINT | PK, Required |  | 88021 |  |
| employee_id | INT | Required |  | 1042 |  |
| annual_amount | DECIMAL(12,2) | Label, Required, PII |  | 118000.00 |  |
| currency_code | STRING | Required |  | USD | ISO 4217 code. |
| effective_from | DATE | Required |  | 2026-01-01 |  |
| effective_to | DATE |  |  |  |  |

### Work Location

An office or site where employees work.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| work_location_id | INT | PK, Required |  | 2 |  |
| location_name | STRING | Label, Required |  | Austin office |  |
| city | STRING | Required |  | Austin |  |
| country_code | REFERENCE | Required | → country.country_code | US |  |

### Country

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| country_code | STRING | PK, Required |  | US | ISO 3166-1 alpha-2 code. |
| country_name | STRING | Label, Required |  | United States | English short name. |

### Employment Type

Kinds of employment contract.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| type_code | STRING | PK, Required |  | FT |  |
| type_name | STRING | Label, Required |  | Full-time |  |

### Leave Type

Kinds of time off.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| type_code | STRING | PK, Required |  | VAC |  |
| type_name | STRING | Label, Required |  | Vacation |  |
| is_paid | BOOLEAN | Required |  | true |  |

## Relationships

| Relationship | Source | Target | Cardinality | Forward Label | Reverse Label | Key | Description |
| --- | --- | --- | --- | --- | --- | --- | --- |
| reports_to | Employee | Employee | N:1 | Reports To | Manages | employee.manager_id | The reporting line: each employee has at most one direct manager. |
| member_of | Employee | Department | N:1 | Member Of | Has Member | employee.department_id |  |
| holds | Employee | Position | N:1 | Holds | Held By | employee.position_id | An employee's current position. |
| works_at | Employee | Work Location | N:1 | Works At | Site Of | employee.work_location_id |  |
| headed_by | Department | Employee | N:1 | Headed By | Heads | department.head_employee_id |  |
| position_in | Position | Department | N:1 | In | Has Position | position.department_id |  |
| position_of_job | Position | Job | N:1 | Of Job | Has Position | position.job_id |  |
| history_of | Employment History | Employee | N:1 | History Of | Has History | employment_history.employee_id |  |
| history_position | Employment History | Position | N:1 | In Position | Held In | employment_history.position_id |  |
| paid_to | Salary | Employee | N:1 | Paid To | Earns | salary.employee_id |  |
| requested_by | Leave Request | Employee | N:1 | Requested By | Requests | leave_request.employee_id |  |
| approved_by | Leave Request | Employee | N:1 | Approved By | Approves | leave_request.approver_id |  |
| review_of | Performance Review | Employee | N:1 | Review Of | Reviewed In | performance_review.employee_id |  |
| reviewed_by | Performance Review | Employee | N:1 | Reviewed By | Reviews | performance_review.reviewer_id |  |

## Reference data

### Country

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

| country_code | country_name |
| --- | --- |
| US | United States |
| CA | Canada |
| GB | United Kingdom |
| DE | Germany |
| FR | France |
| AU | Australia |

### Employment Type

4 values · Source: Example values

| type_code | type_name |
| --- | --- |
| FT | Full-time |
| PT | Part-time |
| CON | Contractor |
| INT | Intern |

### Leave Type

4 values · Source: Example values

| type_code | type_name | is_paid |
| --- | --- | --- |
| VAC | Vacation | true |
| SICK | Sick leave | true |
| PARENT | Parental leave | true |
| UNPAID | Unpaid leave | false |

## 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.

```sql
-- 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.

```mermaid
---
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.