A complete Human Resources Management System (HRMS) database schema designed for managing the full employee lifecycle. Covers organizational hierarchy with departments and positions, employee records, leave request workflows with approval tracking, payroll processing, and performance review cycles. Suitable for building internal HR tools, HRIS platforms, or extending existing HR systems.
employees holds where someone is now; employment_history holds everywhere they have been. The current department_id and position_id are on the employee for fast reads, and every change writes a history row with start_date, end_date and a change_reason — which is what makes tenure, promotion rates and internal mobility answerable.
The reporting line is a self-reference: employees.manager_id points back at employees. One nullable column gives the whole org chart, and a null manager is the top of the tree rather than an error — which also means nothing stops a cycle except the application that writes it.
Leave is two tables because entitlement and request are different facts. leave_types defines what exists and how many days it carries by default; leave_requests is one person asking for dates, with its own status and an approved_by who is another employee.
Deriving the current department from the latest history row is the normalised answer and makes every employee listing an ordered scan per person. Keeping both makes the common read one column and the historical question a join. The obligation is that a change must write both in one transaction, or the employee row and their history will disagree about where they work.
base_salary, bonus and deductions are kept apart so a payslip can be explained line by line and a disputed figure can be traced. One net_pay column would be smaller and would make every query about compensation structure impossible. It also means the arithmetic is frozen — a later salary change does not restate what was actually paid in March.
leave_types carries default_days and leave_requests carries the dates taken, so a balance is a calculation rather than a column. That avoids a counter that drifts whenever a request is cancelled or back-dated. The cost is that the calculation has to handle carry-over, pro-rata for new joiners and accrual rules, none of which the schema expresses — an entitlements table per employee per year is the usual addition.
manager_id and reviewer_id both point at employees, which keeps one identity for a person who manages some people and is managed by another. The limitation is that the schema cannot express a dotted-line or a second manager, and nothing prevents a reporting cycle — both are application responsibilities here.