Many-to-many with GSI overloading in DynamoDB - Mini Project: Build & Apply

Many-to-many relationship with GSI overloading in DynamoDB

📖 Scenario: You are building a simple event management system where users can attend multiple events, and each event can have multiple attendees. You want to store this many-to-many relationship efficiently in a single DynamoDB table using Global Secondary Index (GSI) overloading.

🎯 Goal: Create a DynamoDB table schema and items to represent users, events, and their attendance relationships using GSI overloading. Then write queries to fetch all events a user is attending and all users attending an event.

📋 What You'll Learn

Create a DynamoDB table called EventAttendance with PK and SK as primary keys.

Insert user items with PK as USER#userId and SK as PROFILE#userId.

Insert event items with PK as EVENT#eventId and SK as DETAILS#eventId.

Insert attendance items with PK as USER#userId and SK as EVENT#eventId.

Create a GSI named GSI1 with GSI1PK and GSI1SK to overload keys for querying both users by event and events by user.

Write queries to get all events for a user and all users for an event using the GSI.

💡 Why This Matters

🌍 Real World

Many applications need to represent complex relationships like users attending events, products in orders, or students in classes. DynamoDB single-table design with GSI overloading helps store and query these efficiently.

💼 Career

Understanding many-to-many modeling and GSI usage is essential for backend developers and cloud engineers working with DynamoDB in real-world projects.

1

Create the DynamoDB table and insert user and event items

Create a DynamoDB table called EventAttendance with primary keys PK and SK. Insert two user items with PK as USER#u1 and USER#u2, and SK as PROFILE#u1 and PROFILE#u2. Insert two event items with PK as EVENT#e1 and EVENT#e2, and SK as DETAILS#e1 and DETAILS#e2.

DynamoDB

# Create table EventAttendance and insert user and event items
# Your code here

Hint

Use the pattern PK and SK with prefixes USER# and EVENT# to distinguish item types.

2

Add attendance items and define GSI keys for overloading

Insert attendance items representing users attending events. For user u1 attending event e1 and event e2, and user u2 attending event e1, create items with PK as USER#userId and SK as EVENT#eventId. Add attributes GSI1PK and GSI1SK to overload keys: set GSI1PK to EVENT#eventId and GSI1SK to USER#userId for attendance items.

DynamoDB

EventAttendance table with items:
{
  "PK": "USER#u1",
  "SK": "PROFILE#u1",
  "Name": "Alice"
}
{
  "PK": "USER#u2",
  "SK": "PROFILE#u2",
  "Name": "Bob"
}
{
  "PK": "EVENT#e1",
  "SK": "DETAILS#e1",
  "Title": "DynamoDB Workshop"
}
{
  "PK": "EVENT#e2",
  "SK": "DETAILS#e2",
  "Title": "AWS Summit"
}
# Add attendance items with GSI1PK and GSI1SK
# Your code here

Hint

Attendance items use PK as user and SK as event. GSI keys are reversed to allow querying by event.

3

Write a query to get all events attended by a user

Write a DynamoDB query to get all events attended by user u1. Use the primary key PK with value USER#u1 and filter items where SK begins with EVENT#.

DynamoDB

EventAttendance table with items and query to get events for user u1
# Your code here

Hint

Use begins_with on SK to filter attendance items for the user.

4

Write a query to get all users attending an event using the GSI

Write a DynamoDB query using the GSI named GSI1 to get all users attending event e1. Use GSI1PK with value EVENT#e1 and filter items where GSI1SK begins with USER#.

DynamoDB

# Your code here

Hint

Use the GSI with GSI1PK and GSI1SK to query by event.

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