Why I Redesigned Our DynamoDB Schema Into One Table (And What Almost Went Wrong)

The original DynamoDB layout for Payroo's payroll-compliance platform looked exactly like what a relational background trains you to build: one table for employers, one for employees, one for pay runs, one for payslips, one for timesheet entries. It's a completely reasonable instinct, and it's the wrong instinct for DynamoDB, because it imports a design principle — normalize first, query second — from a database that has joins into one that doesn't.

What breaks with one table per entity

DynamoDB has no join. "Give me an employee along with their recent pay runs and payslips" against five separate tables means five separate round trips, stitched together in application code, on every request that needs that view. Worse, it means you lose transactional guarantees across related writes — finalizing a pay run while also updating an employee's opening balance touches two tables, and keeping those two writes consistent with each other becomes something your application has to coordinate by hand instead of something the database guarantees for you.

None of this is a DynamoDB limitation in the sense of a missing feature. It's a signal that the schema is modeled around entities instead of around the questions the application actually asks.

Designing around access patterns, not entities

Single-table design inverts the order relational modeling teaches: instead of normalizing entities and figuring out queries afterward, you enumerate every access pattern the application actually needs first, and let the key structure fall out of that list. The partition key (PK) and sort key (SK) are deliberately generic and reused across different conceptual entity types, structured so that related items land in the same partition:

PK                    SK                  Item
EMPLOYER#123          EMPLOYEE#456        (employee record)
EMPLOYER#123          PAYRUN#2026-06-30   (pay run)
EMPLOYER#123          PAYRUN#2026-07-31   (pay run)
EMPLOYER#123          METADATA            (employer profile)

"Give me this employer's pay runs, most recent first" becomes a single query — partition key equals the employer, sort key begins with PAYRUN#— returned already sorted by date, because the sort key's lexical ordering does that work for free. No fan-out across tables, no application-side joining, one request.

Access patterns that don't fit the primary key's hierarchy — "find all payslips for a given employee across every employer they've worked for," for instance, which needs the employee as the partition instead of the employer — get a Global Secondary Index with an inverted key shape, rather than a second table. The data lives once; the index gives you a second way to ask questions of it.

The consistency win that was the actual point

Because related entities live in the same table, and often in the same item collection, DynamoDB's TransactWriteItemscan update several related items atomically in one call. Finalizing a pay run and adjusting an employee's opening balance can now happen as a single all-or-nothing operation instead of two writes to two tables that could succeed independently and leave the system in a half-updated state if the second one failed. That was the concrete, measurable improvement from this migration — not fewer tables for their own sake, but consistency guarantees the previous layout structurally couldn't offer.

Where it almost went wrong

The tempting mistake, mid-migration, was designing the key structure to be maximally "flexible" before every access pattern was actually known — guessing at future query shapes and baking in generality for patterns that might never arrive. That instinct is backwards for this kind of migration specifically, because changing a partition key strategy after real data already exists in production isn't a schema tweak, it's a full data migration, item by item, usually while the system is still live. Getting the key design right requires the unglamorous work of listing every real access pattern first — every screen, every report, every filter the product actually needs — and deriving the keys from that list, rather than treating the key design as a creative exercise in anticipating a future that hasn't been specified yet.

The trade-off nobody mentions until a new engineer joins

A table full of generically-named PK/SKattributes holding several different conceptual entity types is opaque in a way one-table-per-entity never is. Open the table in a console and you can't tell what's in it without already knowing the key conventions. That opacity is the real cost of single-table design, and it doesn't show up in a migration plan — it shows up three months later when a new engineer joins and has no way to reverse-engineer the schema from the table itself. The only fix is treating the access-pattern list and an entity-relationship map as first-class documentation, maintained alongside the schema, not as a one-time design artifact that gets thrown away once the migration ships.

The actual unlearning

The hard part of this migration wasn't DynamoDB's API. It was that years of relational modeling teach "normalize first, figure out queries later" as a near-universal good habit, and DynamoDB rewards close to the opposite discipline: know your queries before you design your keys, and treat denormalization and data duplication as normal, not as a smell to be refactored away. Getting comfortable with single-table design was less about learning new syntax and more about deliberately setting aside modeling instincts that had been reliable everywhere else.