Many-to-Many Relationships in DynamoDB
A student enrolls in many courses; a course holds many students. In SQL you reach
for a join table and a three-table JOIN.
DynamoDB has no joins, so the relationship has to live in the keys: each
enrollment is stored in a shape that both sides can Query directly.
How do you model a many-to-many relationship in DynamoDB?
Store each link as its own edge item keyed by one side, then add an inverted GSI that swaps the keys. That is the adjacency list pattern, applied here to two entity types with data on the edge.
The adjacency list guide covers the mechanics and costs in depth.
- Store each enrollment as its own edge item, not a list attribute on either side.
- Key the edge by the student (
PK = STU#…,SK = ENROLL#CRS#…) so oneQueryreturns a student's whole course list. - Add an inverted GSI that swaps the roles (
GSI1PK = CRS#…) so the same edge also answers "who's in this course?". - Put the course record in that index too, so the course page is one
Queryas well.
Frame the access patterns first
DynamoDB modeling is access-pattern-first: you decide the reads before you pick a single attribute name. A many-to-many relationship almost always has two symmetric reads plus the entity lookups:
- Get a student's profile, and list every course that student is enrolled in.
- Get a course's metadata, and list every student enrolled in that course.
- Look up a single enrollment edge, to update a grade or drop the course.
The two list reads point in opposite directions across the same set of edges. A naive
design serves one cheaply and forces a Scan for the other, the footgun covered in
Query vs Scan. The job is to make both directions
a single Query.
Why edge items and an index
The primitive DynamoDB gives you is a Query against one partition: it reads a
contiguous range of sort-key values under one partition key. That is the only "join"
the engine offers.
So you write each enrollment once, keyed by the student, and let a secondary index hold a second copy keyed by the course. You never write that copy; DynamoDB maintains it, and bills the index write.
AWS documents this on its many-to-many modeling guide.
It's the overloaded-key thinking from Single-Table Design, applied to a relationship instead of a parent-child hierarchy. Two stacked views of the same edges:
Work the example: students ↔ courses
Use one table with generic keys, PK and SK, and encode the entity type in the
value. Every entity's own record sits at SK = META; the enrollment edges sit beside
the student's:
| PK | SK | attributes |
|---|---|---|
| STU#a91 | ENROLL#CRS#cs101 | enrolledOn, grade |
| STU#a91 | ENROLL#CRS#math204 | enrolledOn, grade |
| STU#a91 | META | name, year, major |
| STU#b30 | ENROLL#CRS#math204 | enrolledOn, grade |
| STU#b30 | META | name, year, major |
| CRS#cs101 | META | title, credits, term |
| CRS#math204 | META | title, credits, term |
A single Query PK = "STU#a91" returns the student's enrollments and profile
together, one 1 MB page at a time. Narrow it with SK begins_with "ENROLL#" to get
just the course edges. That solves "list a student's courses".
"List a course's students" points the other way. The base table can't answer it: the
course id appears only in the sort key, and a Query has to name the partition key.
Add an inverted global secondary index, GSI1, keyed on a generic
GSI1PK/GSI1SK pair. Each edge holds the course in GSI1PK and the student in
GSI1SK. Give the course's META item GSI1PK = its own id and GSI1SK = META:
| PK | SK | GSI1PK | GSI1SK |
|---|---|---|---|
| CRS#math204 | META | CRS#math204 | META |
| STU#a91 | ENROLL#CRS#math204 | CRS#math204 | STU#a91 |
| STU#b30 | ENROLL#CRS#math204 | CRS#math204 | STU#b30 |
| CRS#cs101 | META | CRS#cs101 | META |
| STU#a91 | ENROLL#CRS#cs101 | CRS#cs101 | STU#a91 |
Now Query GSI1 WHERE GSI1PK = "CRS#math204" returns the course record and then
every enrolled student, since META sorts before STU#. Add
begins_with(GSI1SK, "STU#") when you want the roster alone.
It has to be a GSI, not an LSI. An LSI shares the base table's partition key, and the course partition is a different key entirely; see GSI vs LSI.
The course direction is eventually consistent: a new
enrollment usually reaches the index within a fraction of a second, and a GSI rejects
ConsistentRead.
For read-your-write right after enrolling, read the base edge with GetItem
(PK = STU#a91, SK = ENROLL#CRS#math204, ConsistentRead: true), or merge the
enrollment you just wrote into the roster on the client.
Write and read it in DynoTable
Writing the enrollment means setting four key attributes plus the edge's own data.
An attribute_not_exists(PK) condition on the edge's Put stops a student from
enrolling twice in the same course: it fails when an item with that exact PK and
SK already exists.
You can assemble that condition visually with the
DynamoDB Expression Builder instead of
hand-writing the ExpressionAttributeNames and placeholder values.
In DynoTable you pick GSI1 in the index picker, set GSI1PK = "CRS#math204", and the
course plus its roster come back as a table. Flip the sort-key pill to descending to
reverse it. A row opens in the item editor as the index returned it, so under the
INCLUDE projection you see only the projected attributes. To edit an enrollment,
query the base table on the row's PK and SK and open the base item.

Pitfalls and next steps
- Don't store one side as a list attribute. A
courseIdsarray on the student item gives a course no way to list its roster and hits the 400 KB item ceiling. Read-modify-write it and two enrollments clobber each other too. - Keep edge data on the edge. The enrollment's
gradeandenrolledOnbelong on the edge item, so there's exactly one item per (student, course) pair to update. Copy a field such as the student's name onto it only if the roster must show it. - Project only what the course page needs. An
INCLUDEprojection namingtitle,enrolledOnand any copied name keeps the index small.KEYS_ONLYreturns ids alone; fetch the records withBatchGetItemon theirMETAkeys.
To go deeper, read Single-Table Design for overloaded keys and the adjacency list guide for write cost, index lag and hot targets.
To start from your own relationships, the free Single-Table Design tool turns an access-pattern list like "list a student's courses / list a course's students" into a PK/SK/GSI plan with example items.
Then download DynoTable to model the students ↔ courses schema for real:
create the table and its GSI1, write the edges in the item editor, and query both
directions of the relationship without a single scan.


