Many-to-many with GSI overloading in DynamoDB - Step-by-Step Execution

Concept Flow - Many-to-many with GSI overloading

Write item with PK, SK, and GSI keys

↓

Store item in main table

↓

GSI indexes item by GSI PK and GSI SK

↓

Query main table by PK and SK for one side

↓

Query GSI by GSI PK and GSI SK for other side

↓

Combine results to get many-to-many relationships

Items are stored with primary keys and overloaded GSI keys to represent many-to-many links. Queries use main keys or GSI keys to find related items from either side.

Execution Sample

DynamoDB

PUT item: {PK: 'USER#1', SK: 'GROUP#A', GSI1PK: 'GROUP#A', GSI1SK: 'USER#1'}
PUT item: {PK: 'USER#2', SK: 'GROUP#A', GSI1PK: 'GROUP#A', GSI1SK: 'USER#2'}
QUERY PK='USER#1' to find groups
QUERY GSI1PK='GROUP#A' to find users

Store user-group links with PK as user and SK as group, and GSI1PK as group and GSI1SK as user to enable querying from both sides.

Execution Table

Step	Action	Input	Index Used	Result
1	Put item	{PK:'USER#1', SK:'GROUP#A', GSI1PK:'GROUP#A', GSI1SK:'USER#1'}	Main Table	Item stored
2	Put item	{PK:'USER#2', SK:'GROUP#A', GSI1PK:'GROUP#A', GSI1SK:'USER#2'}	Main Table	Item stored
3	Query	PK='USER#1'	Main Table	Returns item with SK='GROUP#A'
4	Query	GSI1PK='GROUP#A'	GSI1	Returns items with GSI1SK='USER#1' and 'USER#2'
5	Combine	Results from steps 3 and 4	N/A	User 1 belongs to Group A; Group A has Users 1 and 2
6	Exit	No more queries	N/A	Execution ends

💡 All relevant items stored and queried; many-to-many relationships retrieved

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Main Table Items	empty	[{PK:'USER#1', SK:'GROUP#A', GSI1PK:'GROUP#A', GSI1SK:'USER#1'}]	[{PK:'USER#1', SK:'GROUP#A', ...}, {PK:'USER#2', SK:'GROUP#A', ...}]	[{PK:'USER#1', SK:'GROUP#A', ...}, {PK:'USER#2', SK:'GROUP#A', ...}]	[{PK:'USER#1', SK:'GROUP#A', ...}, {PK:'USER#2', SK:'GROUP#A', ...}]	[{PK:'USER#1', SK:'GROUP#A', ...}, {PK:'USER#2', SK:'GROUP#A', ...}]

Key Moments - 3 Insights

Why do we store the same relationship twice with different keys?

Because DynamoDB queries efficiently only on primary keys or GSIs, storing the relationship with PK=User and GSI1PK=Group lets us query from either side (user to groups or group to users). See steps 1, 2, 3, and 4 in the execution_table.

How does querying the GSI help find all users in a group?

The GSI is set with GSI1PK as the group ID and GSI1SK as the user ID, so querying GSI1PK='GROUP#A' returns all users linked to that group. This is shown in step 4.

What happens if we query the main table with a group ID as PK?

It returns no results because the main table's PK is the user ID, not the group ID. To find users by group, we must query the GSI. This is why GSI overloading is needed.

Visual Quiz - 3 Questions

Look at the execution_table, what is returned when querying PK='USER#1' at step 3?

A Items with SK='GROUP#A'

B Items with SK='USER#1'

C Items with GSI1PK='GROUP#A'

D No items

💡 Hint

Check step 3 in the execution_table under Result column

At which step do we query the GSI to find all users in a group?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look at the Index Used column in the execution_table

If we remove GSI1PK and GSI1SK from items, what changes in the execution?

A We can still query users by group using the main table

B We can query groups by user using GSI

C We cannot query users by group efficiently

D No change in querying

💡 Hint

Refer to key_moments about why GSI overloading is needed

Concept Snapshot

Many-to-many with GSI overloading in DynamoDB:
- Store each link twice: PK=User, SK=Group and GSI1PK=Group, GSI1SK=User
- Query main table by PK to find groups for a user
- Query GSI by GSI1PK to find users in a group
- Enables efficient queries from both sides
- GSI overloading means using GSIs to index relationships differently

Full Transcript

This visual execution shows how to model many-to-many relationships in DynamoDB using GSI overloading. Each relationship is stored as an item with primary keys (PK and SK) representing one side (user to group) and GSI keys (GSI1PK and GSI1SK) representing the other side (group to user). We put two items linking users and groups. Then, querying the main table by PK='USER#1' returns groups for that user. Querying the GSI by GSI1PK='GROUP#A' returns all users in that group. This approach allows efficient queries from either side of the many-to-many relationship. The execution table traces each step, showing how items are stored and queried. The variable tracker shows how the main table items grow after each put. Key moments clarify why storing the relationship twice is necessary and how the GSI enables querying by group. The visual quiz tests understanding of querying steps and the role of GSI keys. The concept snapshot summarizes the pattern for quick reference.

Practice

1. What is the main advantage of using a Global Secondary Index (GSI) for many-to-many relationships in DynamoDB?

easy

A. It duplicates data across multiple tables for backup.

B. It allows querying related items from both sides without extra tables.

C. It automatically creates foreign key constraints.

D. It enforces strict schema validation on items.

Solution

1. Step 1: Understand GSI role in many-to-many

   A GSI lets you query items using alternate keys, enabling access from either side of a relationship.

2. Step 2: Compare with other methods

   Unlike extra tables or joins, GSI overloading stores relationships in one table, simplifying queries.

3. Final Answer:

   It allows querying related items from both sides without extra tables. -> Option B

4. Quick Check:

   GSI enables two-way queries without extra tables [OK]

Hint: GSI lets you query both sides in one table [OK]

Common Mistakes:

• Thinking GSI enforces foreign keys
• Assuming GSI duplicates data in multiple tables
• Believing GSI validates schema strictly

2. Which of the following is the correct way to define a GSI for overloading many-to-many relationships in DynamoDB?

easy

A. { "IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "KEYS_ONLY"}}

B. { "IndexName": "GSI1", "KeySchema": [{"AttributeName": "PK", "KeyType": "HASH"}, {"AttributeName": "SK", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}

C. { "IndexName": "GSI1", "KeySchema": [{"AttributeName": "SK", "KeyType": "HASH"}, {"AttributeName": "PK", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "INCLUDE", "NonKeyAttributes": ["Name"]}}

D. { "IndexName": "GSI1", "KeySchema": [{"AttributeName": "ID", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}

Solution

1. Step 1: Identify correct GSI key schema for many-to-many

   The GSI should use SK as HASH and PK as RANGE keys to support querying from both sides.

2. Step 2: Check projection type

   Projection INCLUDE ensures required attributes like Name are available in the index for queries.

3. Final Answer:

   Definition with SK as HASH, PK as RANGE, and Projection INCLUDE -> Option C

4. Quick Check:

   GSI keys SK HASH + PK RANGE + Projection INCLUDE [OK]

Hint: GSI keys: SK as HASH, PK as RANGE, Projection INCLUDE [OK]

Common Mistakes:

• Using only HASH key without RANGE
• Swapping PK and SK roles incorrectly
• Choosing Projection KEYS_ONLY when full data needed

3. Given a DynamoDB table with items storing user-group relationships using GSI overloading, what will this query return?

Query({
  IndexName: 'GSI1',
  KeyConditionExpression: 'SK = :groupId',
  ExpressionAttributeValues: { ':groupId': 'GROUP#123' }
})

medium

A. All users belonging to group with ID 'GROUP#123'.

B. All groups that user 'GROUP#123' belongs to.

C. All items with primary key 'GROUP#123'.

D. An error because PK cannot be used in GSI queries.

Solution

1. Step 1: Understand GSI key usage in query

   The query uses GSI1 with SK = 'GROUP#123', so it fetches items where GSI partition key (SK attribute) equals this group ID.

2. Step 2: Interpret the meaning of PK in many-to-many

   SK in GSI represents the group, PK (range) the users, so we get users in that group.

3. Final Answer:

   All users belonging to group with ID 'GROUP#123'. -> Option A

4. Quick Check:

   Query by GSI SK = group ID returns users in group [OK]

Hint: GSI SK query with group ID returns users in that group [OK]

Common Mistakes:

• Confusing group ID with user ID in query
• Expecting groups for a user instead of users for a group
• Thinking PK cannot be queried in GSI

4. You wrote this query to get all groups for a user but it returns no results:

Query({
  IndexName: 'GSI1',
  KeyConditionExpression: 'SK = :userId',
  ExpressionAttributeValues: { ':userId': 'USER#456' }
})

What is the likely mistake?

medium

A. The GSI's HASH key is actually SK, not PK, for user queries.

B. The query syntax is invalid; KeyConditionExpression must use SK.

C. ExpressionAttributeValues should use ':user' instead of ':userId'.

D. You must use the main table's primary key, not GSI keys.

Solution

1. Step 1: Recall GSI key overloading pattern

   In many-to-many GSI overloading, use main table (PK=userId as HASH) for user-to-groups queries, GSI (SK=groupId as HASH) for group-to-users.

2. Step 2: Check query KeyConditionExpression

   The query uses GSI with SK = userId, but SK holds group IDs, so no results; use main table instead.

3. Final Answer:

   You must use the main table's primary key, not GSI keys. -> Option D

4. Quick Check:

   Main table PK=user for user queries [OK]

Hint: Main table for user->groups, GSI for group->users [OK]

Common Mistakes:

• Using wrong GSI key attribute in query
• Confusing main table keys with GSI keys
• Incorrect ExpressionAttributeValues naming

5. You want to store many-to-many relationships between authors and books in one DynamoDB table using GSI overloading. Which design correctly supports querying all books by an author and all authors of a book efficiently?

hard

A. Use PK = 'AUTHOR#<authorId>', SK = 'BOOK#<bookId>' for author-to-book items, and GSI with PK = SK, SK = PK to query from book side.

B. Create separate tables for authors and books, then join them in application code.

C. Use PK = 'BOOK#<bookId>', SK = 'AUTHOR#<authorId>' only, and no GSI.

D. Store all relationships as JSON arrays inside author items without GSI.

Solution

1. Step 1: Understand many-to-many with GSI overloading

   Store relationships with PK as author and SK as book to query books by author.

2. Step 2: Use GSI to invert keys for reverse query

   Define GSI with PK = SK and SK = PK to query authors by book efficiently.

3. Step 3: Evaluate other options

   Separate tables or JSON arrays do not provide efficient two-way queries without extra work.

4. Final Answer:

   Use PK = 'AUTHOR#<authorId>', SK = 'BOOK#<bookId>' and GSI with PK = SK, SK = PK. -> Option A

5. Quick Check:

   Overload GSI keys to invert PK and SK for two-way queries [OK]

Hint: Invert PK and SK in GSI for reverse many-to-many queries [OK]

Common Mistakes:

• Not using GSI for reverse lookup
• Storing relationships only one way
• Using separate tables instead of one with GSI