Many-to-many with GSI overloading in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query to find all orders for a given customer using GSI overloading

Given a DynamoDB table with a many-to-many relationship between Customers and Orders using a Global Secondary Index (GSI) that overloads the partition key to store both customer and order data, which query will return all orders for customer with ID C123?

DynamoDB

Table: Orders
Partition Key: PK
Sort Key: SK
GSI1 Partition Key: GSI1PK
GSI1 Sort Key: GSI1SK

Data example:
PK = 'CUSTOMER#C123', SK = 'PROFILE'
PK = 'ORDER#O456', SK = 'DETAILS'
GSI1PK = 'CUSTOMER#C123', GSI1SK = 'ORDER#O456'

Query options:

A Scan main table filtering PK = 'CUSTOMER#C123' and SK begins_with 'ORDER#'

B Query GSI1 with KeyConditionExpression: GSI1PK = 'CUSTOMER#C123' and begins_with(GSI1SK, 'ORDER#')

C Query main table with KeyConditionExpression: PK = 'CUSTOMER#C123' and SK begins_with 'ORDER#'

D Query GSI1 with KeyConditionExpression: GSI1PK = 'ORDER#O456' and GSI1SK begins_with 'CUSTOMER#'

💡 Hint

Remember that GSI1 is designed to link customers to their orders by overloading the partition key.

Option B correctly queries the GSI where the partition key is the customer ID and the sort key starts with 'ORDER#', returning all orders for that customer. Option B queries the main table but orders are not stored under the customer's PK. Option B is inefficient and incorrect because orders are not stored under the customer's PK in the main table. Option B reverses the keys and will not return the correct data.

🧠 Conceptual

intermediate

Understanding GSI overloading for many-to-many relationships

In a many-to-many relationship modeled in DynamoDB using GSI overloading, what is the main reason for overloading the GSI partition key with different entity prefixes?

A To automatically replicate data across regions

B To reduce the size of the main table by storing data only in the GSI

C To enforce uniqueness of items across different tables

D To enable querying multiple entity types using the same GSI by distinguishing them with prefixes

💡 Hint

Think about how the GSI can be used to query different entities efficiently.

Overloading the GSI partition key with prefixes like 'CUSTOMER#' or 'ORDER#' allows the same GSI to index multiple entity types and enables efficient queries by filtering on these prefixes. The other options do not describe the purpose of overloading.

📝 Syntax

advanced

Identify the syntax error in this DynamoDB query using GSI overloading

Which option contains a syntax error in the DynamoDB query expression to fetch all orders for customer C789 using GSI1?

DynamoDB

Query parameters example:
IndexName: 'GSI1'
KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :skprefix)'
ExpressionAttributeValues: { ':pk': 'CUSTOMER#C789', ':skprefix': 'ORDER#' }

A KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :skprefix)'

B ')xiferpks: ,KS1ISG(htiw_snigeb dna kp: = KP1ISG' :noisserpxEnoitidnoCyeK

C KeyConditionExpression: 'GSI1PK = :pk and GSI1SK begins_with :skprefix'

D KeyConditionExpression: 'GSI1PK = :pk and GSI1SK = :skprefix'

💡 Hint

Check the syntax for the begins_with function in DynamoDB expressions.

Option C is invalid because begins_with is a function and must be called as begins_with(attribute, value). Writing GSI1SK begins_with :skprefix is a syntax error. Options A and C are correct syntax. Option C is valid but does not filter by prefix.

❓ optimization

advanced

Optimizing queries for many-to-many relationships with GSI overloading

You want to optimize read performance when querying all customers linked to a specific order using a GSI that overloads keys. Which design choice improves query efficiency?

A Use a GSI with partition key as 'ORDER#orderId' and sort key as 'CUSTOMER#customerId' to query customers by order

B Use a scan operation on the main table filtering by order ID prefix

C Store customer and order data in separate tables to avoid GSI overloading

D Use a GSI with partition key as 'CUSTOMER#customerId' and sort key as 'ORDER#orderId' to query customers by order

💡 Hint

Think about how partition keys affect query speed and filtering.

Option A is correct because it allows querying all customers for a given order by partitioning on the order ID, which is efficient. Option A is inefficient because scans are costly. Option A avoids GSI overloading but adds complexity and does not optimize queries. Option A is reversed and does not efficiently query customers by order.

🔧 Debug

expert

Debugging missing results in many-to-many GSI queries

You have a DynamoDB table with many-to-many relationships using GSI overloading. You run this query on GSI1 to get all orders for customer C555:

QueryIndex: 'GSI1'
KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :prefix)'
ExpressionAttributeValues: { ':pk': 'CUSTOMER#C555', ':prefix': 'ORDER#' }

But the query returns no results, even though orders exist. What is the most likely cause?

A The GSI1PK attribute values for orders linked to customer C555 are missing or incorrect in the table items

B The KeyConditionExpression syntax is invalid and causes the query to fail silently

C The main table partition key is used instead of the GSI partition key in the query

D The ExpressionAttributeValues use wrong prefixes like 'ORDER-' instead of 'ORDER#'

💡 Hint

Check if the GSI attributes are correctly populated for linked items.

If the GSI1PK attribute is missing or not set to 'CUSTOMER#C555' on the order items, the query on GSI1 will return no results. The syntax in the query is correct, so option A is unlikely. Option A is about the main table but the query uses GSI1 correctly. Option A is a possible typo but less likely if the query runs without error.

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