Single-table design methodology in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Single-Table Design Basics

In DynamoDB single-table design, what is the main reason to use composite primary keys (partition key + sort key)?

A To allow automatic scaling of read and write capacity without manual configuration

B To store multiple unrelated tables in one physical table without any key constraints

C To uniquely identify each item and enable efficient querying of related items using sort key ranges

D To enforce foreign key constraints between items in the table

💡 Hint

Think about how DynamoDB retrieves items efficiently when you query by partition key and sort key.

Composite primary keys combine partition key and sort key to uniquely identify items and allow queries to fetch related items by sorting within the partition.

❓ query_result

intermediate

Query Result with Composite Keys

Given a DynamoDB table with partition key 'PK' and sort key 'SK', which query will return all orders for customer 'C123' sorted by order date?

Assume items have PK='CUSTOMER#C123' and SK='ORDER#2023-01-01', 'ORDER#2023-02-01', etc.

DynamoDB

Query:
{
  TableName: 'Orders',
  KeyConditionExpression: 'PK = :pk and begins_with(SK, :skprefix)',
  ExpressionAttributeValues: {
    ':pk': 'CUSTOMER#C123',
    ':skprefix': 'ORDER#'
  }
}

A Returns all customers with orders starting with 'ORDER#' prefix

B Returns all orders for customer C123 sorted by date ascending

C Returns all orders for all customers sorted by order date

D Returns no items because begins_with cannot be used with sort key

💡 Hint

Remember that begins_with works on the sort key to filter items within a partition.

The query filters items with PK='CUSTOMER#C123' and SK starting with 'ORDER#', returning all that customer's orders sorted by SK.

📝 Syntax

advanced

Identify the Syntax Error in a DynamoDB Update Expression

Which option contains a syntax error in the DynamoDB UpdateExpression?

UpdateExpression examples:

DynamoDB

A: "SET #status = :newStatus, #count = #count + :inc"
B: "SET #status = :newStatus ADD #count :inc"
C: "SET #status = :newStatus, ADD #count :inc"
D: "SET #status = :newStatus, #count = #count + :inc"

A Syntax error: mixing SET and ADD without separating them properly

B Valid syntax: sets status and increments count with comma separator

C Valid syntax: sets status and adds increment to count using ADD keyword

D Valid syntax: same as A, sets status and increments count

💡 Hint

Check how SET and ADD clauses are combined in UpdateExpression.

In DynamoDB UpdateExpression, multiple actions like SET and ADD must be separated by spaces, not commas. Option A lacks the space between the SET and ADD actions.

❓ optimization

advanced

Optimizing Access Patterns in Single-Table Design

You want to efficiently query all comments for a blog post and also fetch all posts by an author. Which single-table design pattern helps achieve both with minimal queries?

A Use a composite key with PK='POST#postId' and SK='COMMENT#commentId' for comments, and a GSI with PK='AUTHOR#authorId' and SK='POST#postId' for posts

B Create separate tables for posts and comments to avoid complex keys

C Store all comments as a list attribute inside the post item to fetch in one read

D Use a single partition key for all items and filter by type in queries

💡 Hint

Think about how GSIs can support different query patterns in single-table design.

Using a composite key for comments and a GSI keyed by author allows efficient queries for both comments by post and posts by author.

🔧 Debug

expert

Debugging a Query Returning No Results

A query on a DynamoDB single-table design returns no results when trying to fetch all orders for customer 'C789'. The query is:

{
  TableName: 'Sales',
  KeyConditionExpression: 'PK = :pk and SK BETWEEN :start and :end',
  ExpressionAttributeValues: {
    ':pk': 'CUSTOMER#C789',
    ':start': 'ORDER#2023-01-01',
    ':end': 'ORDER#2023-12-31'
  }
}

What is the most likely reason for no results?

A ExpressionAttributeValues keys must start with a letter, so ':start' and ':end' are invalid

B The partition key 'CUSTOMER#C789' does not exist in the table

C BETWEEN operator cannot be used with sort keys in DynamoDB

D The SK values are stored as timestamps without the 'ORDER#' prefix, so BETWEEN does not match

💡 Hint

Check how the sort key values are stored and how BETWEEN compares strings.

If SK values do not include the 'ORDER#' prefix but the query uses it, the BETWEEN condition won't match any items.

Practice

1. What is the main advantage of using single-table design in DynamoDB?

easy

A. It requires multiple tables for each data type to keep data separate.

B. It allows storing different types of related data in one table for faster queries.

C. It automatically creates indexes for all attributes without setup.

D. It stores data only in JSON format for easy reading.

Solution

1. Step 1: Understand single-table design purpose

   Single-table design stores multiple related data types in one table to reduce the number of queries needed.

2. Step 2: Compare options with this purpose

   It allows storing different types of related data in one table for faster queries. matches this purpose. Options B, C, and D describe incorrect or unrelated features.

3. Final Answer:

   It allows storing different types of related data in one table for faster queries. -> Option B

4. Quick Check:

   Single-table design = faster queries with one table [OK]

Hint: Single-table design means one table for related data [OK]

Common Mistakes:

• Thinking single-table means multiple tables
• Assuming automatic index creation
• Confusing data format with design method

2. Which of the following is the correct way to define a partition key in a DynamoDB table using single-table design?

easy

A. PartitionKey: orderId, SortKey: userId

B. PartitionKey: userId#orderId

C. PartitionKey: userId, SortKey: orderId

D. PartitionKey: orderId#userId

Solution

1. Step 1: Recall key structure in single-table design

   Single-table design uses a partition key and a sort key to organize data efficiently.

2. Step 2: Identify correct key assignment

   PartitionKey: userId, SortKey: orderId correctly assigns userId as partition key and orderId as sort key, which is common for grouping orders by user.

3. Final Answer:

   PartitionKey: userId, SortKey: orderId -> Option C

4. Quick Check:

   Partition and sort keys = userId and orderId [OK]

Hint: Partition key groups items; sort key orders them [OK]

Common Mistakes:

• Using combined keys as partition key only
• Swapping partition and sort keys incorrectly
• Using concatenated keys without sort key

3. Given a DynamoDB table with partition key PK and sort key SK, which query will fetch all orders for user with ID 'user123' if orders are stored with PK = 'USER#user123' and SK = 'ORDER#orderId'?

medium

A. Query with KeyConditionExpression: PK = 'ORDER#orderId' AND SK = 'USER#user123'

B. Query with KeyConditionExpression: PK = 'USER#user123' AND SK = 'ORDER#orderId'

C. Scan with FilterExpression: PK = 'USER#user123'

D. Query with KeyConditionExpression: PK = 'USER#user123' AND SK begins_with 'ORDER#'

Solution

1. Step 1: Understand key structure for orders

   Orders have PK as 'USER#user123' and SK starting with 'ORDER#', so all orders share the same PK but different SKs.

2. Step 2: Identify query to fetch all orders for user

   Query with KeyConditionExpression: PK = 'USER#user123' AND SK begins_with 'ORDER#' queries PK equal to 'USER#user123' and SK that begins with 'ORDER#', which fetches all orders for that user.

3. Final Answer:

   Query with KeyConditionExpression: PK = 'USER#user123' AND SK begins_with 'ORDER#' -> Option D

4. Quick Check:

   PK fixed, SK prefix filter = all user orders [OK]

Hint: Use begins_with on sort key to get related items [OK]

Common Mistakes:

• Swapping PK and SK in query
• Using scan instead of query
• Filtering SK with exact match instead of prefix

4. You wrote this query to get all comments for a post in a single-table design DynamoDB table:

KeyConditionExpression: PK = 'POST#123' AND SK = 'COMMENT#'

Why does this query return no results?

medium

A. Because SK uses '=' instead of 'begins_with' to match multiple comments.

B. Because PK should be 'COMMENT#' and SK should be 'POST#123'.

C. Because the table does not support queries on SK.

D. Because the query syntax is missing a FilterExpression.

Solution

1. Step 1: Analyze the KeyConditionExpression

   The query uses SK = 'COMMENT#', which looks for an exact match on SK.

2. Step 2: Understand why no results appear

   Comments likely have SK starting with 'COMMENT#' plus an ID, so exact match fails. Using 'begins_with' on SK is needed to get all comments.

3. Final Answer:

   Because SK uses '=' instead of 'begins_with' to match multiple comments. -> Option A

4. Quick Check:

   Use begins_with for multiple SK matches [OK]

Hint: Use begins_with for partial sort key matches [OK]

Common Mistakes:

• Using '=' instead of begins_with on SK
• Swapping PK and SK in query
• Assuming FilterExpression fixes key condition

5. You want to design a single DynamoDB table to store users, their orders, and order items. Which key design best supports fetching all orders and items for a user efficiently?

hard

A. PartitionKey: USER#userId, SortKey: ORDER#orderId for orders; PartitionKey: ORDER#orderId, SortKey: ITEM#itemId for items

B. PartitionKey: userId, SortKey: orderId#itemId for all data types

C. PartitionKey: USER#userId, SortKey: ORDER#orderId#ITEM#itemId for all items and orders

D. PartitionKey: ITEM#itemId, SortKey: ORDER#orderId for all data

Solution

1. Step 1: Understand data grouping needs

   We want to fetch all orders and items for a user efficiently, so grouping orders by user and items by order is key.

2. Step 2: Evaluate key designs

   PartitionKey: USER#userId, SortKey: ORDER#orderId for orders; PartitionKey: ORDER#orderId, SortKey: ITEM#itemId for items uses USER#userId as partition key for orders and ORDER#orderId as partition key for items, allowing efficient queries by user and order. Other options mix keys or combine too much, making queries inefficient.

3. Final Answer:

   PartitionKey: USER#userId, SortKey: ORDER#orderId for orders; PartitionKey: ORDER#orderId, SortKey: ITEM#itemId for items -> Option A

4. Quick Check:

   Group orders by user, items by order = PartitionKey: USER#userId, SortKey: ORDER#orderId for orders; PartitionKey: ORDER#orderId, SortKey: ITEM#itemId for items [OK]

Hint: Group data by parent keys for efficient queries [OK]

Common Mistakes:

• Using one partition key for all data types
• Combining all keys into one long sort key
• Using item ID as partition key for all data