Employee and Payroll Management ER diagram and database schema

What this schema models

A complete employee management and payroll system database design: 25 tables, 17 enums and 43 relationships covering legal entities, departments and work locations, employee records, effective-dated job assignments and compensation, tax withholding elections, benefits and deductions, time entries, leave requests and accrual, pay schedules and periods, pay runs, itemized pay stubs, and split direct deposits. THE FOUR DESIGN RULES 1. Everything that changes a paycheck is effective-dated. Job assignments, compensation, withholding elections, deductions and home addresses are rows with effective_from and effective_to, never overwritten, so "what was this employee paid on March 15?" and retro pay are exact. 2. Approved payroll is immutable. A run can be recalculated while it is a draft; once approved, a mistake is fixed with a correction or off-cycle run, and a voided paycheck points at the check that reversed it. 3. Pay stubs are itemized snapshots. Every earning, deduction, tax and employer contribution is its own line, with the hours and rate that produced it, and the totals are frozen at approval. 4. Leave balances are a ledger. Accruals, usage, carryover and payouts are entries; a balance is their sum, never a column someone can edit. DETAILS WORTH STEALING - Direct deposit splits across several accounts by priority, with fixed, percentage and remainder allocations. - Time entries record the run that paid them, so approved hours are never paid twice. - Taxes point at jurisdictions (federal, state, local), and both work location and home address matter. - Bank account numbers are stored as tokens with the last four digits, and national IDs as the last four only; full numbers belong in a vault. - Departments nest, so labor cost rolls up the org chart. - Multiple legal entities each run their own payroll. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: organization, employees, employment terms, deductions, time and leave, payroll runs, and pay stubs and deposits. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building HR, HRIS or payroll software, and for students who need a realistic employee management or payroll system ER diagram for a DBMS project. This is a schema design reference, not a tax engine: withholding calculations and compliance rules vary by jurisdiction and belong in your payroll provider or tax service.

Tables (25)

Relationships (43)

• bank_accounts.employee_id references employees.id many to one
• compensation_records.approved_by references employees.id many to one
• compensation_records.employee_id references employees.id many to one
• compensation_records.pay_schedule_id references pay_schedules.id many to one
• departments.legal_entity_id references legal_entities.id many to one
• departments.parent_id references departments.id many to one
• direct_deposits.bank_account_id references bank_accounts.id many to one
• direct_deposits.paycheck_id references paychecks.id many to one
• emergency_contacts.employee_id references employees.id many to one
• employee_addresses.employee_id references employees.id many to one
• employee_deductions.deduction_type_id references deduction_types.id many to one
• employee_deductions.employee_id references employees.id many to one
• employees.legal_entity_id references legal_entities.id many to one
• holidays.legal_entity_id references legal_entities.id many to one
• job_assignments.department_id references departments.id many to one
• job_assignments.employee_id references employees.id many to one
• job_assignments.job_title_id references job_titles.id many to one
• job_assignments.manager_id references employees.id many to one
• job_assignments.work_location_id references work_locations.id many to one
• leave_ledger.employee_id references employees.id many to one
• leave_ledger.leave_request_id references leave_requests.id many to one
• leave_ledger.leave_type_id references leave_types.id many to one
• leave_ledger.pay_run_id references pay_runs.id many to one
• leave_requests.decided_by references employees.id many to one
• leave_requests.employee_id references employees.id many to one
• leave_requests.leave_type_id references leave_types.id many to one
• leave_types.legal_entity_id references legal_entities.id many to one
• pay_periods.pay_schedule_id references pay_schedules.id many to one
• pay_runs.approved_by references employees.id many to one
• pay_runs.pay_period_id references pay_periods.id many to one
• pay_schedules.legal_entity_id references legal_entities.id many to one
• paycheck_lines.paycheck_id references paychecks.id many to one
• paycheck_lines.tax_jurisdiction_id references tax_jurisdictions.id many to one
• paychecks.employee_id references employees.id many to one
• paychecks.pay_run_id references pay_runs.id many to one
• paychecks.voided_by_paycheck_id references paychecks.id many to one
• tax_withholding_elections.employee_id references employees.id many to one
• tax_withholding_elections.tax_jurisdiction_id references tax_jurisdictions.id many to one
• time_entries.approved_by references employees.id many to one
• time_entries.employee_id references employees.id many to one
• time_entries.pay_run_id references pay_runs.id many to one
• work_locations.legal_entity_id references legal_entities.id many to one
• work_locations.tax_jurisdiction_id references tax_jurisdictions.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Employee and Payroll Management database schema?

25 tables and 43 relationships, across 187 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.