Design a Company Payroll Allocation Database
Model multi-department employees, three salary components and account-percentage allocations, then prepare coherent immutable payout snapshots.
Problem statement
Design a relational payroll database and its minimal service boundary for multiple companies and departments. An employee may belong to multiple departments within a company. Salary has three components: Basic, HRA and PA. An employee may direct percentages of salary into multiple bank accounts.
For this exercise, assume each company-employee has one salary, regardless of department count. Salary components are nonnegative integer cents in one currency. One through five employee-owned accounts receive positive integer basis-point shares summing to 10000 (100%).
Replace the employee’s memberships, salary and complete allocation atomically with an expected configuration version. Invalid ownership, amounts, share totals or stale versions leave the prior configuration unchanged. Reads return a coherent configuration and version.
Callers are authenticated and authorized to one company;enforce that scope.
For this exercise, assume prepare(employee, period) creates one immutable paycheck snapshot per company, employee and period. It records one committed salary/allocation version and payout lines;retries return that original snapshot even after configuration changes. It prepares instructions only and never calls a bank.
Floor each exact account share and distribute remaining cents by largest fractional remainder, with ascending opaque account ID breaking ties. The payout lines must sum exactly to Basic+HRA+PA.
Provide a data model, operation contracts and component diagram, then discuss concurrent edits versus preparation.
For this exercise, assume 100 companies, 100000 employees total, up to 10 department memberships and 5 accounts per employee, 200 reads/s and 100 configuration/preparation writes/s at peak;target p95 below 500 ms for healthy single-employee calls.
Tax, legal payroll rules, bank execution, bulk scheduling, amendments, foreign exchange and user interfaces are outside scope.
What to build
- Manage company-scoped employees, many-to-many department memberships, fixed salary components and complete bank-share allocations with coherent versioned reads.
- Atomically replace a complete employee configuration with ownership, share-total, amount and optimistic-version validation.
- Prepare and retrieve one immutable employee-period snapshot with exact-cent payout conservation and stable retry semantics.
At scale
- For this exercise, assume 100 companies and 100000 employees total, up to 10 department memberships and 5 allocation accounts per employee.
- For this exercise, assume 200 reads/s and 100 configuration or preparation writes/s at peak, with p95 below 500 ms for healthy single-employee calls.