Why single-table design matters in DynamoDB - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use single-table design in DynamoDB?

Which of the following best explains why single-table design is important in DynamoDB?

A It reduces the number of tables, improving query efficiency by using composite keys and indexes.

B It forces you to use relational joins like in SQL databases.

C It allows storing data in multiple unrelated tables to avoid complexity.

D It requires creating many tables to separate each entity type.

💡 Hint

Think about how DynamoDB handles queries and indexes.

Single-table design reduces the number of tables and uses composite keys and indexes to efficiently query related data without joins.

❓ query_result

intermediate

Query result with single-table design

Given a DynamoDB table with partition key 'PK' and sort key 'SK', storing users and their orders in the same table, what will this query return?

Query: PK = 'USER#123' AND begins_with(SK, 'ORDER#')

DynamoDB

Table data example:
PK          | SK          | Attribute
USER#123    | ORDER#001   | {amount: 50}
USER#123    | ORDER#002   | {amount: 75}
USER#123    | PROFILE    | {name: 'Alice'}
USER#456    | ORDER#001   | {amount: 20}

A Returns all orders for user 123: ORDER#001 and ORDER#002 items.

B Returns the user profile only.

C Returns all items with PK 'USER#123', including profile and orders.

D Returns orders for all users.

💡 Hint

Look at how the sort key condition filters items.

The query filters items with PK 'USER#123' and SK starting with 'ORDER#', so only orders for that user are returned.

📝 Syntax

advanced

Identify the correct DynamoDB query syntax for single-table design

Which of the following DynamoDB query snippets correctly queries all orders for user with ID '123' using single-table design?

DynamoDB

Assume table has PK as 'PK' and SK as 'SK'.

A query({ KeyConditionExpression: 'PK = :pk AND SK = :sk', ExpressionAttributeValues: { ':pk': 'USER#123', ':sk': 'ORDER#' } })

B query({ FilterExpression: 'PK = :pk AND SK LIKE :sk', ExpressionAttributeValues: { ':pk': 'USER#123', ':sk': 'ORDER#%' } })

C query({ KeyConditionExpression: 'PK = USER#123 AND SK starts_with ORDER#' })

D query({ KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)', ExpressionAttributeValues: { ':pk': 'USER#123', ':sk': 'ORDER#' } })

💡 Hint

Check the correct use of KeyConditionExpression and begins_with function.

Option D uses the correct syntax with KeyConditionExpression and begins_with for the sort key.

❓ optimization

advanced

Optimizing access patterns in single-table design

You want to efficiently retrieve all comments for a blog post and the post's metadata from a single DynamoDB table. Which design approach best supports this?

A Use a global secondary index with the comment ID as partition key.

B Store posts and comments in separate tables to avoid mixing data.

C Use the same partition key for the post and its comments, and different sort key prefixes to distinguish item types.

D Store comments as a JSON array inside the post item.

💡 Hint

Think about how single-table design groups related items.

Using the same partition key groups related items, and sort key prefixes let you query specific item types efficiently.

🔧 Debug

expert

Debugging a failing query in single-table design

A developer runs this DynamoDB query to get all orders for user '123', but it returns no results:

query({
  KeyConditionExpression: 'PK = :pk AND SK = :sk',
  ExpressionAttributeValues: { ':pk': 'USER#123', ':sk': 'ORDER#' }
})

What is the most likely reason?

A The partition key value ':pk' is incorrect and should be 'USER#001'.

B The query uses '=' for SK, but SK values include order IDs like 'ORDER#001', so no exact match occurs.

C The query is missing a FilterExpression to filter orders.

D DynamoDB does not support querying with KeyConditionExpression.

💡 Hint

Consider how sort keys are structured in single-table design.

The query uses '=' on SK, but SK values have order IDs appended, so no exact match with 'ORDER#' exists. Using begins_with is needed.

Practice

1. Why is single-table design important in DynamoDB?

easy

A. It requires more tables to organize data.

B. It stores related data together for faster access.

C. It increases the number of database calls.

D. It makes key design less important.

Solution

1. Step 1: Understand single-table design purpose

   Single-table design groups related data in one table to reduce complexity and speed up queries.

2. Step 2: Compare options with this purpose

   Only It stores related data together for faster access. correctly states it stores related data together for faster access.

3. Final Answer:

   It stores related data together for faster access. -> Option B

4. Quick Check:

   Single-table design = faster access [OK]

Hint: Single-table means fewer tables, faster queries [OK]

Common Mistakes:

• Thinking it increases database calls
• Believing it needs more tables
• Ignoring key design importance

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

easy

A. PartitionKey: userId

B. PartitionKey: {userId, orderId}

C. PartitionKey: userId + orderId

D. PartitionKey: orderId

Solution

1. Step 1: Recall partition key definition

   The partition key must be a single attribute that uniquely partitions data.

2. Step 2: Analyze options

   PartitionKey: userId uses a single attribute userId, which is valid. PartitionKey: {userId, orderId} tries to use multiple attributes as one key, which is invalid syntax. PartitionKey: userId + orderId concatenates keys but is not a direct attribute definition. PartitionKey: orderId uses orderId alone, which may not be unique.

3. Final Answer:

   <code>PartitionKey: userId</code> -> Option A

4. Quick Check:

   Partition key = single attribute [OK]

Hint: Partition key must be a single attribute [OK]

Common Mistakes:

• Using multiple attributes directly as partition key
• Concatenating keys without defining attribute
• Choosing non-unique keys

3. Given a single-table design with partition key UserId and sort key OrderId, what will this query return?

SELECT * FROM Orders WHERE UserId = '123' AND OrderId = 'A1'

medium

A. All orders for user 123

B. All orders with OrderId 'A1' regardless of user

C. Only the order with OrderId 'A1' for user 123

D. Syntax error in query

Solution

1. Step 1: Understand key conditions in query

   The query filters by partition key UserId = '123' and sort key OrderId = 'A1', so it targets one specific item.

2. Step 2: Determine result based on keys

   Since both keys are specified, only the single matching order for user 123 with OrderId 'A1' will be returned.

3. Final Answer:

   Only the order with OrderId 'A1' for user 123 -> Option C

4. Quick Check:

   Partition + sort key = single item [OK]

Hint: Specify both keys to get one item [OK]

Common Mistakes:

• Assuming query returns all orders for user
• Ignoring sort key in query
• Thinking query syntax is invalid

4. You designed a single-table with partition key UserId and sort key OrderId. Your query to get all orders for a user returns no results, but you know data exists. What is the likely mistake?

medium

A. You queried only by sort key without partition key.

B. You used the wrong table name in the query.

C. You queried only by partition key without sort key.

D. You forgot to create the table.

Solution

1. Step 1: Recall query requirements for partition and sort keys

   In DynamoDB, queries must specify the partition key. Querying only by sort key is invalid and returns no results.

2. Step 2: Analyze options

   You queried only by sort key without partition key. matches the mistake of querying only by sort key. You used the wrong table name in the query. and D are possible but less likely if data exists. You queried only by partition key without sort key. is valid and returns all items with that partition key.

3. Final Answer:

   You queried only by sort key without partition key. -> Option A

4. Quick Check:

   Query must include partition key [OK]

Hint: Always include partition key in queries [OK]

Common Mistakes:

• Querying only by sort key
• Using wrong table name
• Assuming table not created

5. In a single-table design, you want to store both user profiles and their orders in one table. Which key design helps you efficiently query all orders for a user and also get the user profile quickly?

hard

A. Partition key: UserId#OrderId, Sort key: ItemType

B. Partition key: OrderId, Sort key: UserId

C. Partition key: ItemType, Sort key: UserId#OrderId

D. Partition key: UserId, Sort key: ItemType#OrderId

Solution

1. Step 1: Understand single-table design key patterns

   Using a partition key as UserId groups all user data together. Adding a sort key with ItemType#OrderId lets you separate user profile and orders.

2. Step 2: Evaluate options for efficient queries

   Partition key: UserId, Sort key: ItemType#OrderId allows querying all orders by filtering sort key starting with 'Order' and fetching profile with sort key 'Profile'. Other options mix keys making queries inefficient or impossible.

3. Final Answer:

   Partition key: UserId, Sort key: ItemType#OrderId -> Option D

4. Quick Check:

   Partition by user, sort by item type [OK]

Hint: Use partition key for user, sort key to separate items [OK]

Common Mistakes:

• Using orderId as partition key
• Not distinguishing item types in sort key
• Combining keys incorrectly