Intermediate7 min read

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 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 one Query returns a student's whole course list.
  • Add an inverted 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 Query as 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:

same edge, swapped keyssame edge, swapped keysInverted GSI1, keyed by courseGSI1PK CRS#math204GSI1SK STU#a91GSI1PK CRS#cs101GSI1SK STU#a91Base table, keyed by studentPK STU#a91SK ENROLL#CRS#math204PK STU#a91SK ENROLL#CRS#cs101

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:

PKSKattributes
STU#a91ENROLL#CRS#cs101enrolledOn, grade
STU#a91ENROLL#CRS#math204enrolledOn, grade
STU#a91METAname, year, major
STU#b30ENROLL#CRS#math204enrolledOn, grade
STU#b30METAname, year, major
CRS#cs101METAtitle, credits, term
CRS#math204METAtitle, 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:

PKSKGSI1PKGSI1SK
CRS#math204METACRS#math204META
STU#a91ENROLL#CRS#math204CRS#math204STU#a91
STU#b30ENROLL#CRS#math204CRS#math204STU#b30
CRS#cs101METACRS#cs101META
STU#a91ENROLL#CRS#cs101CRS#cs101STU#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.

Run the access patterns
Query PK = "STU#a91"

3 of 7 items read — one Query, one partition. The key condition filters before any data leaves it, so you pay only for the matches.

Base table, in stored order
#PKSKGSI1PKGSI1SKAttributes
1STU#a91ENROLL#CRS#cs101CRS#cs101STU#a91enrolledOn, grade
2STU#a91ENROLL#CRS#math204CRS#math204STU#a91enrolledOn, grade
3STU#a91METAname, year, major
STU#b30ENROLL#CRS#math204CRS#math204STU#b30enrolledOn, grade
STU#b30METAname, year, major
CRS#cs101METACRS#cs101METAtitle, credits, term
CRS#math204METACRS#math204METAtitle, credits, term

The items and keys are this guide’s own example. Highlighted rows are what the one call above touches; the numbers show the order they come back in. “Items read” counts items, not read units — real billing rounds each read up to 4 KB blocks.

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 : 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.

Querying the inverted GSI in DynoTable to list every student enrolled in a course.
Querying the inverted GSI in DynoTable to list every student enrolled in a course.

Pitfalls and next steps

  • Don't store one side as a list attribute. A courseIds array 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 grade and enrolledOn belong 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 INCLUDE projection naming title, enrolledOn and any copied name keeps the index small. KEYS_ONLY returns ids alone; fetch the records with BatchGetItem on their META keys.

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.

By Dmitriy · Updated

Try this design interactively

Sketch your entities and access patterns in the free DynamoDB Single-Table Design tool — it suggests PK/SK key templates, previews the item collections, and shows which patterns need a GSI.

Open the Single-Table Design tool