HR & People · data model template

HR / HCM Core data model

Normalized (3NF) HR/HCM core covering org structure, the worker lifecycle, effective-dated assignment and compensation, payroll execution, time off, and benefits. Person identifiers and pay amounts carry PII/confidential classification with masking.

Human-capital-management operational data: the worker, the position/job/department/location structure, effective-dated assignments and compensation, payroll runs and payslips, time-off requests, and benefits enrollments. Worker PII and pay data are classified and masked for GDPR. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.

3NFHR & PeopleGDPRSOC2

15
tables: 132
columns: 16
relationships: 10
PII columns

Use this template About database schema design →

Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.

Subject areas

org_structureworkforcepayrolltime_and_benefits

Design decisions

Effective-dated rows instead of SCD
An OLTP HCM system records assignment and compensation history as separate effective-dated rows (start/end dates), not warehouse SCD columns; this keeps the model 3NF and write-friendly.
Position management separate from job
Headcount positions (seats) are distinct from job profiles (the definition of work); separating them supports open-requisition tracking and FTE budgeting independent of who fills the seat.
Payroll modeled as run -> payslip -> line
Normalizing earnings/deductions into payslip lines referencing a pay-element lookup avoids wide, sparse payslip tables and lets new pay elements be added as data, not schema changes.
PII and pay data classified and masked
National ID and bank account are restricted+full-masked while names/contact are PII+partial; compensation is confidential, satisfying GDPR minimization on the worker record.

Tables in the HR / HCM Core model

Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.

Tables (12)

location

Grain: one row per physical work location

Column	Type	Keys
location_key	bigint	PK
location_code	varchar	BK
location_name	varchar
address_line1	varchar
address_line2	varchar
city	varchar
state_province	varchar
postal_code	varchar
country	varchar
timezone	varchar
is_remote	boolean

department

Grain: one row per organizational department

Column	Type	Keys
department_key	bigint	PK
location_key	bigint
department_code	varchar	BK
department_name	varchar
parent_department_code	varchar
cost_center	varchar
is_active	boolean

job

Grain: one row per job profile / job code

Column	Type	Keys
job_key	bigint	PK
job_code	varchar	BK
job_title	varchar
job_family	varchar
job_level	varchar
flsa_status	varchar
is_management	boolean

position

Grain: one row per headcount position (a seat to be filled)

Column	Type	Keys
position_key	bigint	PK
job_key	bigint
department_key	bigint
location_key	bigint
position_code	varchar	BK
position_title	varchar
fte	decimal
status	varchar
opened_date	date

employee

Grain: one row per worker (person)

Subject worker entity; PII classified for GDPR.

Column	Type	Keys
employee_key	bigint	PK
employee_id	varchar	BK
first_name	varchar	PII
last_name	varchar	PII
preferred_name	varchar	PII
date_of_birth	date	PII
national_id	varchar	PII
gender	varchar	PII
personal_email	varchar	PII
work_email	varchar	PII
phone	varchar	PII
hire_date	date
termination_date	date
employment_status	varchar
worker_type	varchar

assignment

Grain: one row per employee assignment to a position (effective-dated)

Effective-dated, 3NF (no SCD); history kept as separate rows.

Column	Type	Keys
assignment_key	bigint	PK
employee_key	bigint
position_key	bigint
manager_employee_key	bigint
assignment_type	varchar
effective_start_date	date
effective_end_date	date
is_primary	boolean
work_schedule	varchar
standard_hours_per_week	decimal

compensation

Grain: one row per compensation record per assignment (effective-dated)

Column	Type	Keys
compensation_key	bigint	PK
assignment_key	bigint
pay_grade	varchar
base_amount	decimal
currency_code	varchar
pay_frequency	varchar
bonus_target_pct	decimal
effective_start_date	date
effective_end_date	date

payroll_run

Grain: one row per payroll run (a pay cycle execution)

Column	Type	Keys
payroll_run_key	bigint	PK
location_key	bigint
run_code	varchar	BK
pay_group	varchar
period_start_date	date
period_end_date	date
pay_date	date
status	varchar
currency_code	varchar
total_gross	decimal
total_net	decimal

payslip

Grain: one row per employee per payroll run

Column	Type	Keys
payslip_key	bigint	PK
payroll_run_key	bigint
employee_key	bigint
gross_pay	decimal
net_pay	decimal
tax_withheld	decimal
pre_tax_deductions	decimal
post_tax_deductions	decimal
employer_contributions	decimal
bank_account_masked	varchar	PII
pay_date	date

payslip_line

Grain: one row per earning/deduction line on a payslip

Column	Type	Keys
payslip_line_key	bigint	PK
payslip_key	bigint
pay_element_key	bigint
amount	decimal
quantity	decimal
rate	decimal

time_off_request

Grain: one row per time-off request

Column	Type	Keys
time_off_request_key	bigint	PK
employee_key	bigint
time_off_type_key	bigint
approver_employee_key	bigint
start_date	date
end_date	date
hours_requested	decimal
status	varchar
reason	varchar
requested_at	timestamp
decided_at	timestamp

benefit_enrollment

Grain: one row per employee per benefit plan enrollment

Column	Type	Keys
benefit_enrollment_key	bigint	PK
employee_key	bigint
benefit_plan_key	bigint
coverage_level	varchar
enrollment_date	date
coverage_start_date	date
coverage_end_date	date
employee_contribution	decimal
employer_contribution	decimal
status	varchar

Lookup tables (3)

pay_element

Grain: reference list of pay elements (earnings/deductions)

Column	Type	Keys
pay_element_key	bigint	PK
element_code	varchar	BK
element_name	varchar
element_type	varchar
is_taxable	boolean

time_off_type

Grain: reference list of leave/time-off types

Column	Type	Keys
time_off_type_key	bigint	PK
type_code	varchar	BK
type_name	varchar
is_paid	boolean
accrual_method	varchar

benefit_plan

Grain: reference list of benefit plans offered

Column	Type	Keys
benefit_plan_key	bigint	PK
plan_code	varchar	BK
plan_name	varchar
plan_type	varchar
provider_name	varchar

Relationships

Each relationship is a foreign key on the first table referencing the second.

department.location_key → location (many-to-one)
position.job_key → job (many-to-one)
position.department_key → department (many-to-one)
position.location_key → location (many-to-one)
assignment.employee_key → employee (many-to-one)
assignment.position_key → position (many-to-one)
compensation.assignment_key → assignment (many-to-one)
payroll_run.location_key → location (many-to-one)
payslip.payroll_run_key → payroll_run (many-to-one)
payslip.employee_key → employee (many-to-one)
payslip_line.payslip_key → payslip (many-to-one)
payslip_line.pay_element_key → pay_element (many-to-one)
time_off_request.employee_key → employee (many-to-one)
time_off_request.time_off_type_key → time_off_type (many-to-one)
benefit_enrollment.employee_key → employee (many-to-one)
benefit_enrollment.benefit_plan_key → benefit_plan (many-to-one)

Related templates

All templates →

Start for $1

From requirements to AI-ready context, in one platform.

Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.

Get started Book a demo

Plans from $1/month · cancel any time