Around 30.3 million people were paid through PAYE Real Time Information in June 2026, and every one of those payments carried a Full Payment Submission that had to reach HMRC on or before payday [1] [2]. Real Time Information has required this since April 2013, replacing the old habit of reporting once at year end [3]. A payroll data model is the structure that has to carry all of it.
This article is written for developers and product teams building or integrating payroll. It sets out the core entities of a UK payroll data model, the identifiers that hold the records together, the statutory fields HMRC expects, and the effective-dating patterns that make payroll data behave correctly over time.
The reason a UK payroll data model is harder than a generic HR schema is that the shape of the data is fixed by legislation, not by product choice. The fields an employer must report, the way an employee is matched inside HMRC's systems, and the length of time records must be kept are all defined by HMRC and by employment law. A model that ignores those constraints will pass its own tests and then fail at the point of submission.
Key takeaways
- A UK payroll data model has to satisfy HMRC's Real Time Information requirements first, which means the reportable fields are defined by specification, not by product preference [2].
- The employment, not the person, is the unit that HMRC identifies, so the model separates a person from each of their employments [4].
- The payroll ID is the key that links a record across submissions, and changing it incorrectly can create a duplicate employment inside HMRC [4].
- Statutory values such as tax codes, National Insurance category letters and student loan plans are best modelled as coded, effective-dated fields rather than free text [5].
- PAYE records must be kept for at least three years after the end of the tax year they relate to, so a payroll data model needs a retention and history layer, not just a live state [6].
Why a UK payroll data model starts with HMRC, not the database
Most data models begin with the objects a product wants to manage. A UK payroll model has to begin with what HMRC requires an employer to report, because those requirements define the minimum viable schema. Every time an employer pays someone, the software has to send a Full Payment Submission listing pay, tax, National Insurance and any student loan or pension deductions for that employee in that period [2]. If a field is not captured in the model, it cannot be reported, and the submission is either rejected or wrong.
HMRC publishes the exact fields expected in an RTI submission in its guidance for payroll software developers, alongside technical specifications for National Insurance and PAYE tax routines and a set of payroll test data developers can run their software against [7] [8]. Software that submits RTI at scale carries the HMRC Recognised badge, which is the market entry requirement for any UK payroll product, not an optional extra [3].
The practical consequence for a data model is that the reportable fields form a fixed core, and the product's own fields sit around that core. A developer designing an HMRC-recognised payroll API treats the RTI data items as the non-negotiable centre of the schema and builds convenience fields on top, never underneath.
The core entities of a UK payroll data model
A workable UK payroll model rests on a small number of entities with clear relationships. The temptation to collapse them into a single flat employee table is the most common early mistake, because it makes the person and the employment indistinguishable, and HMRC treats those as different things [4].
Employer and PAYE scheme
At the top of the model sits the employer, and attached to the employer is one or more PAYE schemes. A PAYE scheme is the unit HMRC registers, and it carries two references that every submission and payment depends on. The Employer PAYE reference identifies the scheme on submissions and correspondence, in the format of a three digit tax office number, a forward slash and a tax office employer reference, for example 123/AB456 [9]. The Accounts Office reference identifies the employer when paying tax and National Insurance to HMRC, and is always 13 characters [9].
These two references are not interchangeable, and a payroll data model has to store them as distinct, validated fields on the scheme. The table below sets out the difference.
| Reference | Format | Used for |
|---|---|---|
| Employer PAYE reference | 3 digits, slash, then reference (e.g. 123/AB456) | Identifying the scheme on RTI submissions and forms [[9]](https://design.tax.service.gov.uk/hmrc-design-patterns/employer-paye-reference/) |
| Accounts Office reference | 13 characters (e.g. 123PA00012345) | Identifying the employer when paying HMRC each month [[9]](https://design.tax.service.gov.uk/hmrc-design-patterns/employer-paye-reference/) |
A larger organisation can run several PAYE schemes, and a bureau runs schemes for many separate employers, so the relationship from employer to scheme is one to many, and the scheme, not the employer, is the anchor for every submission [3]. Accountants managing this across dozens of clients typically rely on a multi-client payroll dashboard that keeps each scheme's references cleanly separated.
Employee, person and employment
Below the scheme sits the part of the model that most often goes wrong. HMRC identifies an employment, not simply a person. The same individual can hold two jobs with the same employer, or leave and be rehired, and each of those is a separate employment with its own record [4]. HMRC matches an employee across RTI returns using a combination of their name, date of birth, National Insurance number, address and, where present, the payroll ID for that employment [4].
The clean way to model this is to separate the person from the employment. The person entity holds the stable identity attributes, name, date of birth, National Insurance number and address. The employment entity holds everything specific to a job, the start date, the payroll ID, the tax code, the National Insurance category and the pay pattern. This separation matters because a person who is rehired must be given a new, unique payroll ID for the new employment, so the employment record has to be able to exist more than once for a single person [4].
Directors add a further wrinkle. A director is assessed for National Insurance on an annual basis even when paid monthly, so the employment record has to carry a flag that switches the National Insurance calculation method [10]. Modelling a director as an ordinary employee, without that flag, is a frequent source of miscalculated National Insurance in owner-managed companies [10].
Pay periods, payruns and payslips
The third layer of the model handles the flow of pay over time. A pay period defines the frequency, weekly, fortnightly, four-weekly or monthly. A payrun processes a set of employments for one pay period and produces, for each, a payslip and a line in the Full Payment Submission [2]. The employer must give employees a payslip, and the payrun record is where the reportable figures for that period are frozen [3]. Running payroll is itself a defined HMRC process of recording pay, calculating deductions and reporting to HMRC on or before payday, and the payrun entity is the model's representation of one turn of that cycle [14].
The relationship here is one to many in two directions. One employment has many payslips over its life, and one payrun has many payslips across the employments it covers. A payroll data model that treats a payslip as a child of both the employment and the payrun keeps the reporting and the history consistent, which is what an SME payroll platform needs to reconcile a period after the fact.
Identifiers that hold the model together
Relationships in a payroll model are only as reliable as the keys that express them. Two families of identifier do most of the work, and both are defined outside the product.
The payroll ID and RTI matching
The payroll ID, sometimes called the works number, is the reference an employer assigns to an employment and includes on the Full Payment Submission [4]. Its only firm requirement is that it is unique to each employee and each employment [4]. Inside HMRC's systems, the payroll ID is one of the items used to match a submission to an existing employment rather than creating a new one [2].
This is where a data model earns its keep. If a payroll ID changes, the software must report the change by supplying the old payroll ID in a dedicated field and setting a change indicator, or HMRC may treat the submission as a brand new employment and duplicate the employee [4]. A model that stores only the current payroll ID, with no memory of the previous value, cannot generate a correct change of payroll ID. The employment entity therefore needs to hold both the current and the prior payroll ID, at least for the duration of the changeover.
Employer references and scheme keys
The scheme-level references described earlier are the second family of identifier, and they belong on the scheme entity rather than being copied onto every employee. The Employer PAYE reference and Accounts Office reference are validated on entry, because a malformed reference will fail at submission or misdirect a payment [9]. Storing them once, on the scheme, and referencing them from each payrun keeps the model normalised and prevents the drift that happens when the same value is duplicated across thousands of employee rows [3].
Modelling statutory calculations as data
A UK payroll model does not just store figures, it stores the coded inputs that drive statutory calculations. Getting these fields right is the difference between a model that can compute a payslip and one that can only record a number someone typed in.
Tax codes and National Insurance category letters
The tax code determines how much income tax is deducted, and it is a structured code, not a rate. A standard code for the 2026-27 tax year is 1257L, reflecting the £12,570 Personal Allowance, while codes such as BR, D0 and D1 apply flat rates, K codes signal a negative allowance, and an S or C prefix marks Scottish or Welsh taxpayers [5]. Because the meaning is encoded in the string, the model should store the code as a validated field and interpret it in the calculation layer, never flatten it to a single percentage [5].
National Insurance category letters work the same way. The category letter on an employment selects the rate and the reliefs that apply, and the letters are grouped by circumstance, as the table below shows [10].
| Category group | Example letters | What it signals |
|---|---|---|
| Standard | A, B, C, J | Standard employer rate above the Secondary Threshold [[10]](https://www.gov.uk/national-insurance-rates-letters) |
| Under 21 and apprentices | M, H, Z | Zero employer National Insurance up to the upper threshold [[10]](https://www.gov.uk/national-insurance-rates-letters) |
| Veterans | V | Relief in the first 12 months of civilian employment [[10]](https://www.gov.uk/national-insurance-rates-letters) |
| Freeport and Investment Zone | F, I, S, L and N, E, K, D | Scheme-specific reliefs up to a separate threshold [[10]](https://www.gov.uk/national-insurance-rates-letters) |
Storing the category letter as a coded field, effective-dated so a change part way through a year is captured, lets the calculation engine apply the correct rate and relief for each period without rewriting history [8].
Student loans, pensions and statutory pay
Deductions beyond tax and National Insurance follow the same principle of coded, dated fields. A student loan deduction depends on the plan type, and there are five plans plus a separate postgraduate loan, each with its own threshold, so the employment must store the plan as a code rather than as an amount [11]. When an employee has both a plan and a postgraduate loan, the postgraduate loan is deducted first, a rule the calculation layer applies from the coded fields [11]. This mechanic is covered in more depth in the Moonworkers guide to student loan deductions.
Pensions and auto-enrolment add an assessment layer. The employment needs to record the worker's category, the scheme, the contribution basis and the assessment outcome for each period, because The Pensions Regulator requires employers to assess and, where eligible, automatically enrol workers and keep records of doing so [12]. Statutory payments, from Statutory Sick Pay to the family-related payments, likewise attach to the employment and to specific periods, and the Statutory Sick Pay reform that removed waiting days from 6 April 2026 means every day of a qualifying absence can now carry a payment, so the model must record sickness at day-level granularity [13]. An SME payroll system that models each of these as a dated, coded input can recompute a period correctly when a figure is corrected after the fact.
Effective dating and why payroll data is temporal
The single most important pattern in a payroll data model is effective dating. Payroll is not a snapshot, it is a sequence of periods, and almost every attribute that drives a calculation can change part way through a person's employment. A tax code is reissued, a National Insurance category changes when an employee turns 21, a salary is revised, a student loan plan starts. If the model overwrites the old value, it loses the ability to recompute or explain a past period [5].
The fix is to store the attributes that drive calculations as dated records rather than single mutable fields. A tax code becomes a series of tax code records, each with a valid-from date, so the payrun for any given period selects the value that applied then [5]. The same applies to pay rates, National Insurance categories and pension settings. This is why payroll models resemble an accounting ledger more than a typical CRUD application, they are designed to be queried as of a date, not just as of now, a property that matters most for enterprise payroll running many entities under one integration [2].
Effective dating also underpins corrections. When a figure for an earlier period turns out to be wrong, the correction is reported through a later Full Payment Submission rather than by silently editing the original, so the model needs to keep the original alongside the amendment [2]. A model built on mutable current-state rows cannot represent this cleanly, which is why an evergreen UK payroll engine treats history as a first-class part of the schema.
Record retention and the compliance layer
A UK payroll data model has a legal lifetime that extends well past the live pay period. Employers must keep PAYE records for at least three years after the end of the tax year they relate to, and HMRC accepts those records in either paper or digital form [6]. Other obligations run longer, national minimum wage records and, from 6 April 2026, holiday pay records must be kept for six years, and auto-enrolment records for six years as well [6] [12].
For a data model, this means retention is a design requirement, not an afterthought. Deleting an employment when someone leaves is not an option, because the records behind their final payslips have to survive for years [6]. A soft-delete or archival state, combined with the effective-dated history already described, satisfies the obligation while keeping the live tables lean. The compliance layer also has to preserve what was submitted, so that a query about a past tax year returns what HMRC was told, not what the current schema would produce today [2]. Year-end artefacts sit in the same layer, since the P60 an employer must give each employee still on the payroll at the end of the tax year is a summary derived from the retained period records for that year [15].
Building versus embedding a payroll data model
A team that needs UK payroll inside its own product faces a build or embed decision, and the shape of the data model is central to it. Building the model from scratch means owning every RTI data item, every statutory code table, the effective-dating machinery and the multi-year retention layer, and keeping all of it current as thresholds and specifications change each April [8]. That is a substantial and permanent commitment, because the specification is a moving target.
Embedding an engine inverts the problem. Instead of modelling PAYE, National Insurance, statutory pay and RTI internally, the host platform calls an HMRC-recognised payroll API that already holds the compliant model, and stores only the identifiers and references it needs to link its own records to the engine's. A payroll integration API that merely syncs data with an existing payroll product is not the same thing, an embeddable engine is the payroll product, exposed for another platform to call while its users stay inside the host [3]. For an ERP or HR platform, the second route means the correct data model, including its statutory codes and retention behaviour, arrives as part of the integration rather than as a multi-year internal project. Developers can review the shape of that model directly in the API documentation.
Conclusion
A UK payroll data model is defined from the outside in. HMRC fixes the reportable fields, the way an employment is identified, the coded values that drive statutory calculations and the number of years records must survive, and the model has to satisfy all of it before it satisfies any product requirement. The entities are few, employer and scheme, person and employment, pay period, payrun and payslip, but the relationships and the identifiers between them carry real regulatory weight [2] [4].
The pattern that separates a robust payroll model from a fragile one is temporal design. Effective-dated attributes, preserved history and a retention-aware lifecycle turn a set of tables into something that can compute, explain and correct any period on demand. As more HR platforms, ERPs and bureau tools move to deliver payroll inside their own products, the pull is towards embedding a compliant model rather than rebuilding one, so that the statutory shape of the data is inherited rather than maintained by hand.
Frequently asked questions
What is the difference between a person and an employment in a payroll data model?
A person is the individual, identified by stable attributes such as name, date of birth and National Insurance number. An employment is a specific job that person holds with an employer, with its own start date, payroll ID and tax code. HMRC identifies employments rather than people, and the same person can hold more than one employment or be rehired into a new one, so a sound model keeps the two entities separate [4].
Why does the payroll ID matter so much in UK payroll?
The payroll ID is the reference that links an employment across Real Time Information submissions, and it must be unique to each employee and each employment. If it changes without the old value being supplied and the change flagged, HMRC may treat the next submission as a new employment and create a duplicate. Because of this, the data model should retain the previous payroll ID through a changeover, not just the current one [4].
How long does a UK payroll data model need to retain records?
PAYE records must be kept for at least three years after the end of the tax year they relate to, and HMRC accepts them in paper or digital form. Some related records run longer, national minimum wage and, from 6 April 2026, holiday pay records for six years, and auto-enrolment records for six years. A payroll data model therefore needs an archival and history layer rather than deleting records when an employee leaves [6].
Should tax codes and National Insurance categories be stored as text or as coded fields?
They should be stored as validated, coded fields and interpreted in the calculation layer. A tax code such as 1257L or a K code carries structured meaning that a single rate cannot capture, and a National Insurance category letter selects both a rate and any relief. Storing them as effective-dated codes lets the engine apply the correct treatment for each period and recompute past periods accurately [5] [10].
Image prompt for Imagen (also in frontmatter)
Documentary photograph, an overhead three-quarter view of a software engineer's desk in a bright UK office, a single laptop showing a blurred entity-relationship diagram, a paper notebook with hand-sketched boxes and arrows, a cup of tea, a mechanical keyboard, soft natural daylight from a side window, mid-morning, palette of cool grey, navy, warm oak and paper white, calm and uncluttered, asymmetric composition with the laptop in the left two-thirds, shot on a Sony A7 IV at 35mm f/2.8, photojournalism, gentle film grain, no warped hands, no legible text on the screen (screen blurred), landscape orientation 16:9.
