DynamoDB Single-Table Design: Model Many Entities in One Table

Coming from SQL, the instinct is one table per entity: workspaces, documents, comments. In DynamoDB that instinct is often wrong when a screen reads related entities together.

What is single-table design in DynamoDB?

Single-table design stores every entity (workspaces, documents, comments) in one DynamoDB table, distinguished by overloaded partition key and sort key prefixes. Because the keys are designed around your access patterns rather than your entities, a parent and all its children live in one item collection and come back in a single Query, with no joins and no N+1 reads.

One table, many item types per partition

Pick generic key names (PK, SK) and encode the entity type in the value:

Now one Query PK = "WS#acme" returns every document and the workspace record in a single billed read (one page, up to 1 MB), in sort-key order: DOC# sorts before META. SK begins_with "DOC#" narrows it to just the documents.

Visually, the overloaded items stack under one partition key as a single item collection:

Query PK = "WS#acme"

Start from access patterns, not nouns

Single-table design begins with a numbered list of the reads and writes your application performs, and each pattern must compile to a Query or GetItem on the base table or an overloaded GSI.

A frequent pattern that needs a Scan means the keys are wrong. The full walkthrough is in how to model data in DynamoDB.

A three-pattern app might look like:

1. Fetch a workspace + all its documents for one workspace id (high volume).
2. List every document awaiting review across workspaces, sorted by date (moderate volume).
3. Fetch one document by its share-link id (high volume).

Pattern 1 is a base-table Query on PK = WS#<id>. Pattern 2 lands on GSI1PK = STATUS#REVIEW with GSI1SK as the date (the overloaded GSI below).

Pattern 3 might use PK = DOC#<id>, SK = META as a separate item collection from pattern 1's WS#<id> / DOC#<id> rows. Duplicate the document summary into both if both paths are hot, or accept a second Query when one path is rare.

One GSI serves several patterns

The same trick works on indexes. Put a generic GSI1PK/GSI1SK on items, and a single GSI serves multiple access patterns depending on what each item writes into those attributes:

Now Query GSI1 WHERE GSI1PK = "STATUS#REVIEW" lists documents in review by date, a pattern the base table can't answer. A different entity can reuse GSI1 with its own meaning (e.g. workspaces by plan under PLAN#team). One index, many queries.

Every document in review shares that one GSI key value, so all their index writes land on one key. That is fine at moderate volume. Near a partition's 1,000 write units per second, shard it (STATUS#REVIEW#0..N-1) and query each shard, as hot partitions shows.

Many-to-many: the adjacency list

For relationships (a user in many teams, a team with many users), write the edge twice with the ids swapped: PK=USER#1, SK=TEAM#9 and PK=TEAM#9, SK=USER#1. Querying either side lists the other, the DynamoDB stand-in for a join table.

Read cost: one Query vs many GetItems

Suppose a workspace record (1 KB) and twelve document summaries (1 KB each) share PK = WS#acme. One eventually consistent Query reads 13 KB, which rounds up to four 4 KB blocks at 0.5 read units each: 2 units.

Thirteen GetItem calls round each item up on its own: 13 × 0.5 = 6.5 units.

A BatchGetItem of the 13 keys saves the round trips but meters the same 6.5. Two tables keyed by workspace id (a GetItem for the workspace, a Query for the 12 KB of documents) cost 0.5 + 1.5 = 2 units in two requests.

So the cost gap is per-item reads and Scans, not table count. Separate tables pay one rounding per table (at most an extra 0.5 eventually consistent unit per table per read) plus a round trip. Single-table's win is the one request.

Numbers shift with item size (documents at 6 KB each cross two 4 KB blocks per item), which is why the item-size calculator sits next to the design tool. Writes obey a 1 KB rounding boundary (one write unit per kilobyte, doubled inside transactions), so fat document rows cost more to denormalize onto the parent partition.

Type every item, and omit index keys to keep it off a GSI

Add a string Type attribute (EntityType, with values like Workspace and Document) so application code can branch when a Query returns mixed sort-key prefixes.

GSIs can be sparse: only items that carry GSI1PK/GSI1SK appear on the index. A published document (GSI1PK/GSI1SK absent, not empty: DynamoDB rejects an empty string as an index key) drops out of the review queue and uses no index storage.

When two entity types share an index, document what each prefix means (STATUS#, PLAN#, EMAIL#) in the same place you document the base-table prefixes.

When not to single-table

Single-table design is a trade: you buy one-request reads with a schema that is harder to read and to change. Separate tables fit better for heavy analytics, plain CRUD over unrelated entities, and entities that scale or fail independently.

For analytics, keep the operational table and query an export: turn on PITR, run ExportTableToPointInTime to S3 (it consumes no read capacity), then query the files with Athena. The full decision, with a flowchart, is in when not to use single-table design.

Cost of the wrong shape

Separate tables mean one request per table and a client-side stitch to reassemble a workspace. A Scan only appears when a child table isn't keyed by the parent id, and that is the Scan footgun. Model the access patterns first, then design keys to make each one a Query.

For the ad-hoc question you never modeled for, DynoTable's SQL Workbench (paid plans) runs filters, GROUP BY and aggregates client-side.

Its JOIN works across separate tables when the to-side attribute is that table's partition key (on a table without a sort key) or a GSI partition key. It rejects a self-join inside one single-table layout, so there you Query the item collection instead.

Evolving a live single-table layout usually means adding GSIs or new item types, not renaming PK/SK semantics in place. Treat prefix contracts like API versions: add DOC#v2# rather than repurpose DOC# mid-flight.

When every item's keys must change, run it as a migration.

Deploy writers that dual-write the new key attributes, backfill old items with a throttled parallel Scan and a conditional UpdateItem (attribute_exists(PK) AND attribute_not_exists(GSI2PK), so an item deleted after the Scan read it is not recreated as a key-only stub), add GSI2 online, then switch reads. DynamoDB migrations walks each step.

Sketch the design with the free Single-Table Design tool: it turns your pattern list into a PK/SK/GSI layout with example items, CreateTable JSON and cost hints, so you can compare alternatives before committing. Estimate what these items cost per read with the item-size & capacity calculator.

Compose the resulting Query programs in the query builder when you're ready to ship SDK code. If partition + sort key mechanics still feel fuzzy, read composite primary keys: single-table design is mostly disciplined composite-key usage with shared attribute names.

Try DynoTable to browse a single-table schema: run a key query on WS#acme and the grid lists that item collection in sort-key order.