One-to-many relationship patterns in DynamoDB - Step-by-Step Execution

Concept Flow - One-to-many relationship patterns

Start with Parent Item

↓

Store Parent with PK

↓

Add Child Items

↓

Child Items share Parent PK + unique SK

↓

Query by Parent PK

↓

Retrieve Parent + all Children

↓

Use results to show one-to-many

This flow shows how a parent item and multiple child items are stored and queried in DynamoDB using a shared partition key and unique sort keys to represent one-to-many relationships.

Execution Sample

DynamoDB

PK = 'USER#123'
SK = 'PROFILE'
// Parent item
PutItem(PK, SK, {name: 'Alice'})

// Child items
PutItem(PK, 'ORDER#1', {item: 'Book'})
PutItem(PK, 'ORDER#2', {item: 'Pen'})

// Query all for PK
Query(PK)

Stores a user as parent and their orders as children sharing the same PK but different SKs, then queries all items for that user.

Execution Table

Step	Action	PK	SK	Item Data	Result
1	Put parent item	USER#123	PROFILE	{name: 'Alice'}	Parent stored
2	Put child item 1	USER#123	ORDER#1	{item: 'Book'}	Child 1 stored
3	Put child item 2	USER#123	ORDER#2	{item: 'Pen'}	Child 2 stored
4	Query items by PK	USER#123	ALL	N/A	Returns parent + 2 children
5	Process results	N/A	N/A	N/A	One-to-many relationship shown
6	End	N/A	N/A	N/A	No more steps

💡 All items for PK 'USER#123' retrieved, showing one parent and multiple children

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
PK	undefined	USER#123	USER#123	USER#123	USER#123	USER#123
SK	undefined	PROFILE	ORDER#1	ORDER#2	ALL	ALL
Items Stored	0	1 (parent)	2 (parent + child1)	3 (parent + 2 children)	3 (queried)	3 (final)
Query Result	empty	empty	empty	empty	[parent, child1, child2]	[parent, child1, child2]

Key Moments - 3 Insights

Why do all child items share the same PK as the parent?

Because in DynamoDB one-to-many patterns, the PK groups related items together so querying by PK returns the parent and all its children (see execution_table step 4).

How do we distinguish between parent and child items if they share the same PK?

We use different SK values: the parent has a unique SK like 'PROFILE', children have SKs like 'ORDER#1', 'ORDER#2' (see execution_table steps 1-3).

What happens when we query by PK only?

We get all items with that PK, including the parent and all children, showing the one-to-many relationship (see execution_table step 4).

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the SK of the second child item stored?

A PROFILE

B ORDER#2

C ORDER#1

D USER#123

💡 Hint

Check the SK column in execution_table row for step 3

At which step does the query return all related items for the parent?

A Step 4

B Step 2

C Step 1

D Step 5

💡 Hint

Look at the Result column in execution_table for step 4

If the SK for child items were not unique, what would happen when querying by PK?

A Parent item would be missing

B All child items would still be returned correctly

C Only one child item would be returned

D Query would fail

💡 Hint

Refer to key_moments about SK uniqueness and execution_table steps 2-3

Concept Snapshot

One-to-many in DynamoDB:
- Use same PK for parent and children
- Use unique SKs to distinguish items
- Query by PK returns parent + all children
- SK pattern helps identify item type
- Efficient retrieval of related data

Full Transcript

This visual execution shows how to model one-to-many relationships in DynamoDB. We start by storing a parent item with a partition key (PK) and a sort key (SK). Then we add child items that share the same PK but have unique SKs. When we query by the PK, DynamoDB returns the parent and all child items together. This pattern groups related data and allows efficient retrieval. The SK helps us tell parent and children apart. The execution table traces each step: storing parent, storing children, querying, and processing results. The variable tracker shows how PK, SK, and stored items change over time. Key moments clarify why PK is shared and SK is unique. The quiz tests understanding of SK values, query results, and consequences of SK duplication.

Practice

1. What is the main purpose of using a PartitionKey in a one-to-many relationship pattern in DynamoDB?

easy

A. To group all related items under one main item

B. To uniquely identify each related item

C. To store the data type of the item

D. To define the size of the table

Solution

1. Step 1: Understand PartitionKey role

   The PartitionKey groups related items together in DynamoDB, acting like a folder for related data.

2. Step 2: Differentiate PartitionKey from SortKey

   The SortKey uniquely identifies each item within the group, but PartitionKey groups them all.

3. Final Answer:

   To group all related items under one main item -> Option A

4. Quick Check:

   PartitionKey groups related items = D [OK]

Hint: PartitionKey groups items; SortKey identifies each item [OK]

Common Mistakes:

• Confusing PartitionKey with SortKey
• Thinking PartitionKey uniquely identifies each item
• Assuming PartitionKey stores data type

2. Which of the following is the correct way to define a SortKey in a DynamoDB table for a one-to-many relationship?

easy

A. SortKey as the PartitionKey

B. SortKey as the same value for all items

C. SortKey as a unique identifier for each related item

D. SortKey as the table name

Solution

1. Step 1: Understand SortKey purpose

   The SortKey uniquely identifies each related item within the same PartitionKey group.

2. Step 2: Eliminate incorrect options

   SortKey cannot be the same for all items or equal to PartitionKey or table name.

3. Final Answer:

   SortKey as a unique identifier for each related item -> Option C

4. Quick Check:

   SortKey uniquely identifies items = C [OK]

Hint: SortKey uniquely identifies related items within a PartitionKey [OK]

Common Mistakes:

• Using same SortKey for all items
• Confusing SortKey with PartitionKey
• Assigning table name as SortKey

3. Given a DynamoDB table with PartitionKey = 'CustomerID' and SortKey = 'OrderID', what will the query Query(CustomerID='123') return?

medium

A. Only the order with OrderID 123

B. All orders for customer with ID 123

C. All customers with OrderID 123

D. An error because SortKey is missing

Solution

1. Step 1: Understand Query with PartitionKey only

   Querying with PartitionKey='123' returns all items sharing that PartitionKey, i.e., all orders for customer 123.

2. Step 2: Clarify SortKey role in query

   SortKey is not specified, so all SortKey values under PartitionKey='123' are returned.

3. Final Answer:

   All orders for customer with ID 123 -> Option B

4. Quick Check:

   Query by PartitionKey returns all related items = A [OK]

Hint: Query by PartitionKey returns all related items [OK]

Common Mistakes:

• Thinking query returns single item without SortKey
• Confusing PartitionKey and SortKey in query
• Expecting error when SortKey is not specified

4. You have a DynamoDB table with PartitionKey = 'PostID' and SortKey = 'CommentID'. You try to insert two comments with the same PostID and CommentID. What is the issue?

medium

A. The second insert overwrites the first comment

B. Both comments are stored separately

C. DynamoDB throws a syntax error

D. PartitionKey must be unique, so insert fails

Solution

1. Step 1: Understand uniqueness of PartitionKey + SortKey

   In DynamoDB, the combination of PartitionKey and SortKey must be unique for each item.

2. Step 2: Analyze inserting duplicate keys

   Inserting an item with the same PartitionKey and SortKey overwrites the existing item.

3. Final Answer:

   The second insert overwrites the first comment -> Option A

4. Quick Check:

   Duplicate keys overwrite existing item = A [OK]

Hint: Duplicate PartitionKey+SortKey overwrites existing item [OK]

Common Mistakes:

• Expecting error on duplicate keys
• Thinking both items are stored separately
• Confusing PartitionKey uniqueness with SortKey

5. You want to model a one-to-many relationship where each customer can have multiple orders, and each order can have multiple items. Which DynamoDB design pattern best fits this scenario?

hard

A. Use PartitionKey as CustomerID and SortKey as OrderID#ItemID

B. Use PartitionKey as OrderID and SortKey as CustomerID#ItemID

C. Use PartitionKey as ItemID and SortKey as CustomerID#OrderID

D. Use PartitionKey as CustomerID#OrderID and SortKey as ItemID

Solution

1. Step 1: Understand the hierarchy

   Customer has many orders, and each order has many items, so two levels of one-to-many.

2. Step 2: Design composite keys for nested relationships

   Using PartitionKey as CustomerID#OrderID groups items by order under each customer; SortKey as ItemID identifies each item.

3. Step 3: Eliminate other options

   Other options mix keys incorrectly, losing the hierarchical grouping.

4. Final Answer:

   Use PartitionKey as CustomerID#OrderID and SortKey as ItemID -> Option D

5. Quick Check:

   Composite PartitionKey groups nested one-to-many = B [OK]

Hint: Use composite PartitionKey for nested one-to-many groups [OK]

Common Mistakes:

• Using wrong key order losing hierarchy
• Not combining keys for nested relationships
• Confusing PartitionKey and SortKey roles