PostgreSQL

HR and employee database schema

A core HR schema: employees, positions, compensation history, time-off balances, and reporting relationships. Compensation and assignments are versioned for audit.

Start for free

Database schema previewHR and employee database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: HR and employee database schema.

A core HR schema: employees, positions, compensation history, time-off balances, and reporting relationships. Compensation and assignments are versioned for audit.

Include these tables: employee, employee_profile, position, assignment, compensation, time_off_balance.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- Employment data is versioned: compensation and assignments keep full history.
- Reporting lines are tracked over time, not just current.
- PII separated into employee_profile for retention controls.

Patterns

- Versioned tables (assignment, compensation) with effective_on / ended_on ranges.
- PII isolated in employee_profile for retention rules.
- Reporting line lives on assignment, so it changes over time.

Tables

The schema, table by table

employee

DBML - employee

Table employee {
  id           uuid pk
  work_email   text unique not null
  employee_no  text unique not null
  status       text not null   // active, on_leave, terminated
  started_on   date not null
  ended_on     date
}

employee_profile

PII kept separate so retention and access can be controlled.

DBML - employee_profile

Table employee_profile {
  employee_id  uuid [ref: > employee.id] pk
  legal_name   text not null
  date_of_birth date
  phone        text
  address      jsonb
}

position

DBML - position

Table position {
  id          uuid pk
  title       text not null
  department  text not null
  level       text
}

assignment

Versioned: one row per assignment period.

DBML - assignment

Table assignment {
  id           uuid pk
  employee_id  uuid [ref: > employee.id, not null]
  position_id  uuid [ref: > position.id, not null]
  manager_id   uuid [ref: > employee.id]
  effective_on date not null
  ended_on     date
}

compensation

Versioned compensation history.

DBML - compensation

Table compensation {
  id           uuid pk
  employee_id  uuid [ref: > employee.id, not null]
  amount       numeric(12,2) not null
  currency     text not null
  frequency    text not null   // annual, monthly, hourly
  effective_on date not null
  ended_on     date
}

time_off_balance

DBML - time_off_balance

Table time_off_balance {
  employee_id  uuid [ref: > employee.id]
  kind         text not null   // vacation, sick, personal
  as_of        date not null
  days_remaining numeric(5,2) not null
  pk (employee_id, kind, as_of)
}

Relationships

From	To	Type
employee	employee_profile	1:1
position	assignment	1:N
employee	assignment	1:N
employee	compensation	1:N
employee	time_off_balance	1:N

Notes from the field

Treat compensation rows as append-only - never update.
Use a partial index on assignment(ended_on IS NULL) to find current assignments quickly.
For multi-country payroll, add country_code to compensation for tax rules.

Frequently asked

Why version compensation instead of overwriting?

Audit and back-pay calculations need history. Storing the full timeline is the simplest correct answer.

How do I query the current manager?

Filter assignment where ended_on IS NULL and join through manager_id. A view over this is a clean abstraction.

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.