How to Model Many to Many Relationships in DynamoDB

In DynamoDB, model many to many relationships by creating a join table that stores pairs of related item IDs using partition key and sort key. This lets you efficiently query all related items from either side by indexing the join table.

Syntax

To model many to many in DynamoDB, create a join table with two keys: PK and SK. Each item represents a link between two entities.

• PK: The ID of the first entity.
• SK: The ID of the second entity.

This structure allows querying all related items by specifying either PK or SK with a Global Secondary Index (GSI).

sql

CREATE TABLE JoinTable (
  PK STRING,
  SK STRING,
  Attribute1 STRING,
  PRIMARY KEY (PK, SK)
);

-- Create a GSI to query by SK
CREATE GLOBAL SECONDARY INDEX GSI1 ON JoinTable (SK, PK);

Example

This example shows a join table linking Students and Courses in a many to many relationship. Each item stores a student ID as PK and a course ID as SK.

You can query all courses for a student by querying with PK=studentId, or all students for a course by querying the GSI with SK=courseId.

json

PUT JoinTable Item:
{
  "PK": "STUDENT#123",
  "SK": "COURSE#456",
  "EnrollmentDate": "2024-06-01"
}

PUT JoinTable Item:
{
  "PK": "STUDENT#123",
  "SK": "COURSE#789",
  "EnrollmentDate": "2024-06-02"
}

-- Query courses for student 123
Query JoinTable where PK = "STUDENT#123"

-- Query students for course 456 using GSI
Query JoinTable.GSI1 where SK = "COURSE#456"

Output

[
  {"PK": "STUDENT#123", "SK": "COURSE#456", "EnrollmentDate": "2024-06-01"},
  {"PK": "STUDENT#123", "SK": "COURSE#789", "EnrollmentDate": "2024-06-02"}
]

[
  {"PK": "STUDENT#123", "SK": "COURSE#456", "EnrollmentDate": "2024-06-01"}
]

Common Pitfalls

Common mistakes include:

• Trying to store many to many links inside a single item attribute, which limits query flexibility and size.
• Not creating a Global Secondary Index (GSI) on the SK attribute, making it hard to query from the second entity side.
• Using ambiguous or inconsistent key naming, which complicates queries.

Always use a join table with clear PK and SK and create a GSI for reverse lookups.

json

/* Wrong: Storing all course IDs in one student item */
{
  "PK": "STUDENT#123",
  "Courses": ["COURSE#456", "COURSE#789"]
}

/* Right: Separate join items for each student-course pair */
{
  "PK": "STUDENT#123",
  "SK": "COURSE#456"
}
{
  "PK": "STUDENT#123",
  "SK": "COURSE#789"
}

Quick Reference

Concept	Description
Join Table	A table storing pairs of related entity IDs as PK and SK.
Partition Key (PK)	ID of the first entity in the relationship.
Sort Key (SK)	ID of the second entity in the relationship.
Global Secondary Index (GSI)	Index on SK to query from the second entity side.
Query	Use PK to find all related items from one side, GSI on SK for the other.

Key Takeaways

Use a join table with PK and SK to represent many to many links in DynamoDB.

Create a Global Secondary Index on SK to query relationships from both sides.

Avoid storing multiple related IDs in a single attribute to keep queries efficient.

Consistent key naming helps maintain clear and simple queries.

Modeling many to many with join tables fits DynamoDB’s strengths in fast key-value lookups.