8. Single-table design
DynamoDB · 8. Single-table designThe most contested idea in the DynamoDB world, and the one that separates people who fight the database from people who find it easy. It is also routinely oversold, so this chapter ends with the case against.
The premise
Section titled “The premise” RELATIONAL MODELLING DYNAMODB MODELLING entities → tables access patterns → keys relationships → foreign relationships → items keys sharing a partition new query → new SELECT new query → possibly a new index or a migrationBecause a Query reads one partition by exact key (chapter 3), anything you want to fetch together must share a partition key. That single constraint produces the entire technique.
The method
Section titled “The method” ① LIST THE ACCESS PATTERNS, exhaustively "get a customer's profile" "list a customer's orders, newest first" "get one order with its line items" "find all pending orders across customers" "look up a user by email"
② For each: what is the input? what comes back? how often? how big?
③ Design keys so each pattern is ONE Query
④ Use GSIs only for patterns the base keys cannot serve (ch 7)
⑤ Write the list down in the repository, beside the table definition. it is the schema.Step ⑤ matters more than it sounds. The access-pattern list is the only documentation of why the keys look the way they do, and without it the next engineer will add a scan.
Overloaded keys
Section titled “Overloaded keys”One table, generic key names, many entity types:
PK SK _type attrs ───────────────────────────────────────────────────── CUSTOMER#42 PROFILE Customer name… CUSTOMER#42 ORDER#2026-01-02#A1 Order total… CUSTOMER#42 ORDER#2026-03-11#A9 Order total… CUSTOMER#42 ADDRESS#home Address city… ORDER#A9 ITEM#01 LineItem sku… ORDER#A9 ITEM#02 LineItem sku… PRODUCT#SKU-1 META Product title… "get everything about customer 42" Query PK = CUSTOMER#42 → 1 request
"just their orders, newest first" Query PK = CUSTOMER#42 AND begins_with(SK, "ORDER#") ScanIndexForward = false → 1 request
"one order and all its line items" Query PK = ORDER#A9 → 1 requestThat is the payoff: the joins a relational schema would perform are already materialised as adjacency in the key space.
The sort key does the hierarchy
Section titled “The sort key does the hierarchy” SK = COUNTRY#DE#CITY#Berlin#STORE#0042
begins_with "COUNTRY#DE#" → all German begins_with "COUNTRY#DE#CITY#Berlin#" → all Berlin = "COUNTRY#DE#CITY#Berlin#STORE#0042" → oneOne sort key, three levels of granularity, all cheap. This is why chapter 2 spent time on key formatting: the prefix hierarchy is the index.
THE RULES THAT MAKE IT WORK • most general segment first • a consistent separator (#) • ISO-8601 dates, so they sort • zero-padded numbers • never a value that can contain the separatorAdjacency lists for many-to-many
Section titled “Adjacency lists for many-to-many” many students, many courses
PK SK _type ──────────────────────────────────────── STUDENT#1 PROFILE Student STUDENT#1 COURSE#MATH101 Enrolment STUDENT#1 COURSE#PHYS200 Enrolment COURSE#MATH101 META Course
GSI1PK GSI1SK ──────────────────────────────────────── COURSE#MATH101 STUDENT#1 ← the index flips COURSE#PHYS200 STUDENT#1 the relationship "which courses is student 1 taking?" Query table, PK = STUDENT#1, begins_with COURSE# "who is taking MATH101?" Query GSI1, GSI1PK = COURSE#MATH101One inverted GSI serves every reversed relationship in the table — the standard pattern, and the reason well-designed single tables need so few indexes.
Denormalisation is the default
Section titled “Denormalisation is the default” RELATIONAL store the customer name once, join to it
DYNAMODB copy the customer name onto the order item, because the order screen needs it and a second lookup costs a round trip THE COST YOU ACCEPT when the customer renames themselves, you update every copy — usually asynchronously, from a stream (ch 10)
THE COST YOU AVOID a second request on every order render, foreverDuplicate the attributes that are read often and change rarely. Do not duplicate volatile values; look those up.
Worked example: a small SaaS
Section titled “Worked example: a small SaaS” ACCESS PATTERNS 1 get tenant settings 2 list a tenant's users 3 get a user by id 4 get a user by email (login) 5 list a user's documents, newest first 6 list all documents pending review, across tenants
TABLE PK SK TENANT#acme SETTINGS (1) TENANT#acme USER#u17 (2,3) USER#u17 DOC#2026-03-11#d9 (5)
GSI1 (overloaded + sparse) GSI1PK GSI1SK EMAIL#a@acme.com USER#u17 (4) REVIEW#pending 2026-03-11T09:00 (6)Six patterns, one table, one index. Pattern 6 is sparse (chapter 7): documents carry GSI1PK = REVIEW#pending only while they await review, and the attribute is removed when they are done.
When not to do this
Section titled “When not to do this”The honest half, and it is not short.
SINGLE-TABLE DESIGN COSTS
✗ LEGIBILITY the console is unreadable, and new engineers cannot explore the data without the pattern list
✗ FLEXIBILITY a genuinely new access pattern may need a migration, not a SELECT
✗ ANALYTICS mixed entity types in one table make downstream processing harder. export to S3 and query there instead. (ch 11)
✗ TOOLING ORMs, admin panels and documentation generators assume one entity per table
✗ ONBOARDING it is genuinely hard to learn, and half-done versions are worse than either extreme USE MULTIPLE TABLES WHEN • entities are truly independent and never fetched together • access patterns are still changing weekly • the team is new to DynamoDB and the traffic is modest — correctness first, cost later • different entities have wildly different capacity or retention needsWriting the design down
Section titled “Writing the design down”The artefact that makes a single-table design maintainable is not the code — it is a table of patterns kept in the repository, next to the infrastructure definition.
| # | Pattern | Index | Key condition | |---|--------------------------|-------|--------------------------------------| | 1 | Tenant settings | table | PK=TENANT#<id> SK=SETTINGS | | 2 | Users in a tenant | table | PK=TENANT#<id> SK begins_with USER# | | 3 | User by id | table | PK=USER#<id> SK=PROFILE | | 4 | User by email (login) | GSI1 | GSI1PK=EMAIL#<addr> | | 5 | A user's documents | table | PK=USER#<id> SK begins_with DOC# | | 6 | Documents pending review | GSI1 | GSI1PK=REVIEW#pending (sparse) | WHY THIS DOCUMENT EARNS ITS PLACE • it is the ONLY explanation of why keys look like that • a new pattern gets added here first, and the review question becomes "can an existing key serve this?" • it makes the scan-shaped mistake visible in code review: no row, no queryKeeping entities legible in code
Section titled “Keeping entities legible in code”# one place that knows the key format — never inlinedef user_pk(user_id: str) -> str: return f"USER#{user_id}"def doc_sk(created: str, doc_id: str) -> str: return f"DOC#{created}#{doc_id}"
class DocumentRepo: def list_for_user(self, user_id, limit=20): return paginate(self.table.query( KeyConditionExpression=Key("PK").eq(user_pk(user_id)) & Key("SK").begins_with("DOC#"), ScanIndexForward=False, Limit=limit))Key construction scattered through a codebase is how a single-table design becomes unmaintainable. One module owns the formats, and everything else calls it — which also makes a future key migration a change in one file plus a backfill, rather than an archaeology project.
Migrating a model later
Section titled “Migrating a model later” ADDING A PATTERN a new GSI, backfilled in the background (ch 7) CHANGING SORT KEYS a new attribute + a new index; migrate readers, then drop the old CHANGING THE PARTITION a new table and a full KEY migration, usually via streams double-writing both tables (ch 10)The third row is the expensive one, and it is exactly why step ① of the method is worth a whole afternoon.
What to take from this chapter
Section titled “What to take from this chapter”- You model from access patterns, not entities, because
Queryreads one partition by exact key — anything fetched together must share one. - Write the access-pattern list down in the repository. It is the real schema.
- Overloaded generic keys (
PK/SK) plus a_typeattribute let one table hold many entity types, so relational joins become adjacency in the key space. - Hierarchical sort keys with consistent separators, ISO dates and zero-padding give several granularities of query from one key.
- Adjacency lists plus one inverted GSI serve many-to-many relationships in both directions.
- Denormalise attributes that are read often and change rarely, and repair copies asynchronously from streams.
- The costs are real: legibility, flexibility, analytics, tooling and onboarding. Multiple tables are right when entities are independent, patterns are still moving, or the team is new.
- The strongest argument for one table is round-trip count, not storage.
- Adding a pattern is usually a new GSI; changing the partition key is a new table and a migration — which is why the pattern list comes first.
Next: transactions and consistency — conditional writes, TransactWriteItems, optimistic locking, and the idempotency that retries demand.