logo
ExamplesEditorPricingDocs
37.1kSign in

HR and payroll database schema

How an HR system models data that must never be overwritten: employees, positions and reporting lines as dated rows, compensation history, payroll runs and itemised payslips, tax withholding, time off as a ledger, and benefits enrolment.

HR Payroll ER diagram

HR and payroll database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 employees idBIGINT employee_numberNVARCHAR legal_first_nameNVARCHAR legal_last_nameNVARCHAR preferred_nameNVARCHAR work_emailNVARCHAR personal_emailNVARCHAR national_id_last_…NCHAR date_of_birthDATE hire_dateDATE termination_dateDATE employment_statusNVARCHAR employment_typeNVARCHAR pay_frequencyNVARCHAR created_atDATETIME2 departments idBIGINT codeNVARCHAR nameNVARCHAR parent_department…BIGINT head_employee_idBIGINT is_activeBIT positions idBIGINT position_codeNVARCHAR titleNVARCHAR job_familyNVARCHAR job_levelSMALLINT is_exemptBIT min_annual_minorBIGINT max_annual_minorBIGINT currency_codeCHAR is_activeBIT employee_assignments idBIGINT employee_idBIGINT position_idBIGINT department_idBIGINT manager_employee_…BIGINT working_titleNVARCHAR fte_ratioDECIMAL location_codeNVARCHAR effective_fromDATE effective_toDATE change_reasonNVARCHAR created_atDATETIME2 compensation_records idBIGINT employee_idBIGINT assignment_idBIGINT pay_typeNVARCHAR annual_amount_min…BIGINT hourly_rate_minorBIGINT currency_codeCHAR bonus_target_bpsINTEGER change_reasonNVARCHAR effective_fromDATE effective_toDATE approved_by_emplo…BIGINT created_atDATETIME2 payroll_runs idBIGINT run_referenceNVARCHAR pay_frequencyNVARCHAR period_startDATE period_endDATE payment_dateDATE statusNVARCHAR employee_countINTEGER gross_total_minorBIGINT net_total_minorBIGINT currency_codeCHAR approved_by_emplo…BIGINT approved_atDATETIME2 created_atDATETIME2 payslips idBIGINT payroll_run_idBIGINT employee_idBIGINT compensation_reco…BIGINT department_idBIGINT gross_pay_minorBIGINT total_deductions_…BIGINT total_tax_minorBIGINT net_pay_minorBIGINT currency_codeCHAR payment_methodNVARCHAR payment_referenceNVARCHAR document_keyNVARCHAR is_adjustmentBIT created_atDATETIME2 payslip_lines idBIGINT payslip_idBIGINT line_kindNVARCHAR codeNVARCHAR descriptionNVARCHAR quantityDECIMAL rate_minorBIGINT amount_minorBIGINT currency_codeCHAR benefit_enrolment…BIGINT relates_to_period…DATE tax_withholdings idBIGINT payslip_idBIGINT jurisdiction_codeNVARCHAR tax_typeNVARCHAR taxable_base_minorBIGINT employee_amount_m…BIGINT employer_amount_m…BIGINT rate_bpsINTEGER currency_codeCHAR filing_periodNCHAR time_off_policies idBIGINT policy_codeNVARCHAR nameNVARCHAR leave_typeNVARCHAR accrual_methodNVARCHAR accrual_hours_per…DECIMAL max_balance_hoursDECIMAL carryover_limit_h…DECIMAL is_paidBIT requires_approvalBIT is_activeBIT time_off_transactio… idBIGINT employee_idBIGINT policy_idBIGINT transaction_kindNVARCHAR hoursDECIMAL effective_dateDATE absence_start_dateDATE absence_end_dateDATE statusNVARCHAR approved_by_emplo…BIGINT payslip_idBIGINT noteNVARCHAR created_atDATETIME2 benefit_plans idBIGINT plan_codeNVARCHAR nameNVARCHAR benefit_typeNVARCHAR provider_nameNVARCHAR employee_cost_min…BIGINT employer_cost_min…BIGINT currency_codeCHAR is_pretaxBIT plan_year_startDATE plan_year_endDATE benefit_enrolments idBIGINT employee_idBIGINT plan_idBIGINT coverage_tierNVARCHAR dependent_countSMALLINT employee_cost_min…BIGINT currency_codeCHAR statusNVARCHAR elected_atDATETIME2 coverage_startDATE coverage_endDATE

Related database schemas

PostgreSQL
Internal tools

Applicant tracking & recruiting database schema

An ATS modelled end to end: jobs with openings and hiring teams, candidates separate from applications, per-job pipeline stages, interviews with panels, structured scorecards, offers with approval chains and a talent pool of near misses.

SQL Server
Internal tools

IT asset management database schema

How an IT estate is tracked end to end: models versus serialised assets, custody history, software licences and seat allocation with compliance counts, warranties and support contracts, maintenance, depreciation, audits and disposal.

PostgreSQL
Internal tools

Sales CRM & pipeline database schema

A sales CRM modelled end to end: accounts and contacts, leads converted into an account, contact and deal, deals moving through stages with full stage history, activities, quotes, forecasts, territories and custom fields.

MySQL
Internal tools

IT helpdesk and service desk database schema

An internal service desk modelled properly: a service catalogue, incidents and requests, ordered approval chains, change requests with risk and rollback plans, linked assets, versioned knowledge base articles, SLA clocks that pause, and satisfaction ratings.