One-to-Many Relationships in DynamoDB

A SaaS control plane almost always has a containment hierarchy: one workspace
owns many projects. In SQL you'd put a workspace_id foreign key on the
projects table and JOIN.

DynamoDB has no joins and no foreign keys, so the relationship has to live in the
key schema itself. Done right, "load a workspace and every project inside it"
becomes a single Query instead of one read plus a follow-up scan.

How do you model a one-to-many relationship in DynamoDB?

Give the parent and all its children the same partition key so they share one item collection, then differentiate them with the sort key. DynamoDB has no joins or foreign keys, so the relationship lives in the key schema itself. Loading a parent plus every child then becomes a single Query instead of a join.

• Model the reads, not the entities. The one-to-many relationship only exists to serve "list a workspace's projects" — shape the keys around that query.
• Encode the parent into the child's partition key. Give the workspace and all its projects the same partition-key value so they land in one item collection.
• Then the list read is one Query. Parent plus its children come back together — no join, no second round trip (a Query returns up to 1 MB per page, paginating via LastEvaluatedKey beyond that).
• Watch the hot partition. One huge tenant concentrates all its traffic on one partition; a giant workspace may need a sharded key and a fan-out read.

The access pattern, first

DynamoDB modeling is access-pattern-first, not entity-first — the same discipline
behind single-table design. Before choosing any
key, write down the reads the app actually issues:

• Get one workspace's settings.
• List every project in a workspace, newest-first.
• Get one specific project by id.

The "one workspace, many projects" relationship only matters because of read #2.
If you never needed to list a workspace's projects together, you wouldn't model
the relationship at all — you'd store projects independently.

So the question is never "how do I represent one-to-many?" in the abstract. It's
"which queries must this relationship serve?" Answer that, then shape the keys
around it.

Why a foreign key won't help here

In DynamoDB every GetItem and Query targets a partition key, and the
service hashes that key to locate the partition holding the item.

AWS says so directly in the Core Components
docs: the partition-key value is the input to an internal hash function that
decides where data lives.

That hash-based placement is the inheritance from the original 2007 Dynamo:
Amazon's Highly Available Key-value Store paper, where consistent hashing
distributes keys across nodes.

A bare workspace_id attribute on a project item is invisible to that
machinery — DynamoDB can't "follow" it.

To fetch related items in one request, the parent's identity must be encoded into
the project's partition key, so all of a workspace's items hash to the same
partition and one Query can sweep them.

Worked example: workspaces and projects

Use a generic, overloaded key schema. Call the partition key EntityRef and the
sort key Detail. The workspace identity goes into EntityRef for both the
workspace item and every project under it:

The workspace and all its projects share EntityRef = "WS#acme", so they form a
single item collection living together on one partition.

The Detail sort key separates them: META is the workspace record, and each
project carries a PROJ# prefix with a zero-padded, time-ordered id so projects
sort naturally.

Visually, the parent and its children stack inside one partition, ordered by the
sort key:

One Query on EntityRef = "WS#acme" sweeps the whole stack — parent plus every
child — in a single read.

Now the three access patterns each collapse to one call:

• Workspace settings — GetItem(EntityRef="WS#acme", Detail="META").
• List projects newest-first — Query(EntityRef="WS#acme") with Detail begins_with "PROJ#", run in descending order (ScanIndexForward = false).
• One project — GetItem(EntityRef="WS#acme", Detail="PROJ#2026-0042").

The second one is the point. The parent and its children come back from
one Query, no join and no second round trip — DynamoDB returns up to 1 MB
per page and hands you a LastEvaluatedKey to fetch the rest. That's the move
you can't make with a foreign-key attribute and a Scan.

Writing that begins_with condition by hand is fiddly — the key-condition and
projection-expression syntax bites.

The DynamoDB Expression Builder generates
the KeyConditionExpression, the #name/:value placeholder maps, and a
ready-to-run SDK snippet so you don't fight the grammar:

Inspect the item collection in DynoTable

Every row sharing an EntityRef is the
workspace plus its children, sitting next to each other.

DynoTable groups them so you see the one-to-many relationship as one contiguous
block instead of guessing at it across separate tables.

Pitfalls and the alternative shape

A few things to watch:

• Hot partitions. Every item for one workspace lives on one partition, so a single very large or very busy tenant concentrates traffic. The adaptive capacity behavior AWS describes absorbs moderate skew, but a workspace with millions of projects may need a sharded key (e.g. WS#acme#01 … #10) and a fan-out read.
• Item-collection size. With a local secondary index, a single partition's item collection is capped at 10 GB; without an LSI there's no such limit. If you're weighing index types here, see GSI vs LSI.
• Reach for Query, never Scan. The whole design exists so you can Query one partition. Falling back to a filtered Scan to "find a workspace's projects" throws the model away and reads the entire table — the trap covered in Query vs Scan.

If you genuinely need to list projects across workspaces (say, all
status = ACTIVE projects globally), the base table can't answer that — its
partition key is workspace-scoped.

That's a job for a secondary index that re-partitions projects on a different
attribute, not for reshaping this relationship.

Next steps

Model the access patterns, encode the parent into the child's partition key, and
the one-to-many read is a single Query. Build and validate the key condition
with the DynamoDB Expression Builder — and
if you'd rather start from the access patterns themselves, the free
Single-Table Design tool drafts the
PK/SK/GSI plan with example items.

Then download DynoTable to load this schema, browse the
workspace→projects item collection live, and confirm each query does exactly one
read. If you'd rather see workspaces and projects as a joined, relational view,
DynoTable's SQL Workbench runs that JOIN too.