How do you model one-to-many and many-to-many relationships in DynamoDB?
Model one-to-many and many-to-many relationships in DynamoDB using item collections, the adjacency-list pattern and inverted GSIs — with code and examples.
Expected Interview Answer
In DynamoDB you model relationships through key design and item collections rather than foreign keys and joins: one-to-many is expressed by placing child items under the parent's partition key with distinct sort keys, and many-to-many is expressed with an adjacency-list pattern plus a Global Secondary Index that inverts the keys.
For one-to-many, the parent and all its children share a partition key (PK=CUSTOMER#1 with SK=PROFILE and SK=ORDER#100, ORDER#101), so a single Query returns the parent with its children. For many-to-many, you store one item per relationship edge (PK=STUDENT#1, SK=COURSE#A) and add an inverted GSI (GSI1PK=COURSE#A, GSI1SK=STUDENT#1); querying the base table lists a student's courses, while querying the GSI lists a course's students. Composite sort keys and secondary indexes let you traverse the relationship from either direction without joins.
- Both sides of a relationship served by a single Query
- No joins or multiple round trips at read time
- Inverted GSI enables bidirectional traversal
- Item collections keep related data physically together
- Scales to large fan-outs with predictable latency
AI Mentor Explanation
A one-to-many bond is a captain and their eleven players filed under one team ID: open the team and every player is listed beneath the captain. Many-to-many is players across multiple squads — the same player turns out for club, state and country — so you keep a card for each player-squad pairing and a reverse index that, given a squad, lists its players and, given a player, lists their squads. Both directions are read without cross-referencing separate books.
Step-by-Step Explanation
Step 1
Model one-to-many with shared partition keys
Give the parent and its children the same PK (CUSTOMER#1) and distinct SKs (PROFILE, ORDER#100) so a Query returns the item collection.
Step 2
Use sort-key prefixes to filter children
Query with begins_with(SK, 'ORDER#') to fetch only orders, or a range on the SK to page and sort children efficiently.
Step 3
Model many-to-many as an adjacency list
Store one item per relationship edge, e.g. PK=STUDENT#1, SK=COURSE#A, capturing the link as its own row.
Step 4
Add an inverted GSI
Create a GSI where GSI1PK=SK and GSI1SK=PK so querying by course returns its students, reversing the traversal direction.
Step 5
Denormalize attributes onto edges if needed
Copy frequently-read fields (course name, enrolled date) onto the edge item to avoid extra lookups when listing either side.
What Interviewer Expects
- Item collections and shared partition keys for one-to-many
- Sort-key prefixes and begins_with for filtering children
- Adjacency-list pattern for many-to-many relationships
- Inverted (flipped-key) GSI for bidirectional traversal
- Understanding denormalization trade-offs on edge items
Common Mistakes
- Trying to emulate SQL foreign keys and joins
- Storing a list of child IDs in one attribute, hitting the 400KB item limit
- Forgetting the inverted GSI, making one traversal direction require a Scan
- Not using sort-key prefixes, so children cannot be filtered efficiently
- Ignoring hot partitions when a parent has an extreme fan-out of children
Best Answer (HR Friendly)
“Instead of linking tables like a traditional database, DynamoDB puts related records close together using smart labels. For a parent with many children you give them the same group label; for links that go both ways, like students and courses, you save each connection and add a second index so you can look it up from either side.”
Code Example
// Edge items: one row per student-course link
// { PK: 'STUDENT#1', SK: 'COURSE#A', GSI1PK: 'COURSE#A', GSI1SK: 'STUDENT#1' }
import { QueryCommand } from '@aws-sdk/lib-dynamodb'
// 1) All courses for a student — query the BASE table
const courses = await ddb.send(new QueryCommand({
TableName: 'AppTable',
KeyConditionExpression: 'PK = :pk AND begins_with(SK, :c)',
ExpressionAttributeValues: { ':pk': 'STUDENT#1', ':c': 'COURSE#' },
}))
// 2) All students in a course — query the INVERTED GSI
const students = await ddb.send(new QueryCommand({
TableName: 'AppTable',
IndexName: 'GSI1',
KeyConditionExpression: 'GSI1PK = :pk',
ExpressionAttributeValues: { ':pk': 'COURSE#A' },
}))Follow-up Questions
- What is the adjacency-list design pattern in DynamoDB?
- How does an inverted index (flipped GSI) enable reverse lookups?
- What are the risks of a very large item collection under one partition?
- When would you denormalize versus keep a separate edge item?
- How do you paginate a one-to-many relationship with many children?
MCQ Practice
1. How is a one-to-many relationship typically modeled in DynamoDB?
Placing the parent and its children under the same partition key forms an item collection retrievable by one Query.
2. Which pattern models many-to-many relationships in DynamoDB?
You store one item per edge and add a GSI that flips the keys, letting you traverse the relationship from either side.
3. Why is storing all child IDs in one attribute risky?
A single item caps at 400KB and cannot be paged or filtered per child, so large relationships need separate items.
Flash Cards
One-to-many in DynamoDB? — Parent and children share a partition key with distinct sort keys, forming an item collection returned by a single Query.
Many-to-many in DynamoDB? — Adjacency-list pattern: one item per relationship edge, plus an inverted GSI to traverse the link from both directions.
What is an inverted GSI? — A Global Secondary Index whose partition/sort keys are the base table's keys swapped, enabling reverse lookups.
Why not store child IDs in one attribute? — Items cap at 400KB and cannot be filtered or paged per child; large relationships need separate edge items.