Hierarchical data modeling in DynamoDB - Step-by-Step Execution

Concept Flow - Hierarchical data modeling

Start with Root Item

↓

Add Child Items with Parent ID

↓

Query by Parent ID to get Children

↓

Repeat for deeper levels

↓

Traverse hierarchy by querying each level

Hierarchical data modeling stores items with parent-child links, allowing queries to retrieve children by referencing parent IDs step-by-step.

Execution Sample

DynamoDB

Table: Employees
PK: EmployeeID
Attributes: Name, ManagerID

Insert:
{EmployeeID: 1, Name: 'Alice', ManagerID: null}
{EmployeeID: 2, Name: 'Bob', ManagerID: 1}
{EmployeeID: 3, Name: 'Carol', ManagerID: 1}

Query: Get employees where ManagerID = 1

This code models employees with managers as parents and queries employees reporting to Alice.

Execution Table

Step	Action	Query Condition	Result Items
1	Insert root employee Alice	N/A	{EmployeeID:1, Name:'Alice', ManagerID:null}
2	Insert child employee Bob	N/A	{EmployeeID:2, Name:'Bob', ManagerID:1}
3	Insert child employee Carol	N/A	{EmployeeID:3, Name:'Carol', ManagerID:1}
4	Query employees with ManagerID=1	ManagerID=1	[{EmployeeID:2, Name:'Bob'}, {EmployeeID:3, Name:'Carol'}]
5	Query employees with ManagerID=2	ManagerID=2	[]
6	End of queries	No more children	No results

💡 No more child employees found for queried ManagerID, traversal ends.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
Employees Table	Empty	Alice, Bob inserted	Alice, Bob, Carol inserted	Alice, Bob, Carol	Alice, Bob, Carol	Alice, Bob, Carol
Query Result	None	None	None	[Bob, Carol]	[]	[]

Key Moments - 2 Insights

Why does querying ManagerID=2 return no results?

Because no employees have ManagerID=2 in the table as shown in execution_table row 5, so the query returns an empty list.

How do we find all employees under Alice?

By querying employees where ManagerID=1 (Alice's EmployeeID), as shown in execution_table row 4, which returns Bob and Carol.

Visual Quiz - 3 Questions

Look at the execution_table, what items are returned when querying ManagerID=1?

A [{EmployeeID:1, Name:'Alice'}]

B [{EmployeeID:2, Name:'Bob'}, {EmployeeID:3, Name:'Carol'}]

C []

D [{EmployeeID:3, Name:'Carol'}]

💡 Hint

Check execution_table row 4 under 'Result Items' for ManagerID=1 query.

At which step does the table contain the employee Carol?

A After Step 3

B After Step 2

C After Step 1

D After Step 4

💡 Hint

Look at execution_table rows 2 and 3 for when Bob and Carol are inserted.

If we add an employee Dave with ManagerID=2, what will querying ManagerID=2 return?

A []

B [{EmployeeID:2, Name:'Bob'}]

C [{EmployeeID:4, Name:'Dave'}]

D [{EmployeeID:3, Name:'Carol'}]

💡 Hint

Adding Dave with ManagerID=2 means querying ManagerID=2 returns Dave, unlike current empty result in execution_table row 5.

Concept Snapshot

Hierarchical data modeling in DynamoDB:
- Store items with a unique ID and a ParentID (e.g., ManagerID)
- Root items have null ParentID
- Query children by filtering on ParentID
- Repeat queries to traverse deeper levels
- Enables tree-like data representation in flat tables

Full Transcript

Hierarchical data modeling in DynamoDB means storing data items with links to their parent items using a field like ManagerID. The root item has no parent (null). When we want to find children of a parent, we query items where the ParentID equals the parent's ID. For example, Alice has EmployeeID 1 and no manager. Bob and Carol have ManagerID 1, so they are Alice's children. Querying for ManagerID=1 returns Bob and Carol. If we query for ManagerID=2, no children exist yet, so the result is empty. This way, we can build and traverse a hierarchy step-by-step by querying each level's children.

Practice

1. What is the main purpose of hierarchical data modeling in DynamoDB?

easy

A. To organize data with parent-child relationships

B. To store unrelated data in separate tables

C. To improve data encryption

D. To increase the size of the database

Solution

1. Step 1: Understand hierarchical data modeling

   Hierarchical data modeling organizes data by showing relationships where one item is the parent and others are children.

2. Step 2: Relate to DynamoDB usage

   DynamoDB uses this model to efficiently store and query related items using keys.

3. Final Answer:

   To organize data with parent-child relationships -> Option A

4. Quick Check:

   Hierarchical data = parent-child organization [OK]

Hint: Think parent and child data linked by keys [OK]

Common Mistakes:

• Confusing hierarchical with unrelated data storage
• Thinking it improves encryption
• Assuming it increases database size

2. Which key should be used as the partition key to model a parent item in DynamoDB hierarchical data?

easy

A. Child ID

B. Parent ID

C. Timestamp

D. Random UUID

Solution

1. Step 1: Identify the role of partition key

   The partition key groups related items together in DynamoDB.

2. Step 2: Choose the key representing the parent

   Since the parent is the main group, its ID should be the partition key to link children under it.

3. Final Answer:

   Parent ID -> Option B

4. Quick Check:

   Partition key = Parent ID for grouping [OK]

Hint: Partition key groups parents; use Parent ID [OK]

Common Mistakes:

• Using Child ID as partition key
• Choosing timestamp which is unrelated
• Using random UUID that breaks grouping

3. Given a DynamoDB table with partition key ParentID and sort key ChildID, what will the query KeyConditionExpression: ParentID = 'P1' return?

medium

A. Only child items with ChildID 'P1'

B. Only the parent item with ID 'P1'

C. All items with ParentID 'P1' including parent and children

D. No items, syntax error in query

Solution

1. Step 1: Understand the query condition

   The query filters items where partition key ParentID equals 'P1'.

2. Step 2: Recall hierarchical data structure

   All items with ParentID 'P1' include the parent and its children because they share the same partition key.

3. Final Answer:

   All items with ParentID 'P1' including parent and children -> Option C

4. Quick Check:

   Query by ParentID returns parent + children [OK]

Hint: Query by partition key returns all related items [OK]

Common Mistakes:

• Thinking only parent is returned
• Confusing sort key with partition key
• Assuming syntax error without cause

4. You wrote a DynamoDB query to get all children of a parent using KeyConditionExpression: ParentID = :pid AND ChildID = :cid. It returns only one child instead of all children. What is the mistake?

medium

A. Using '=' on ChildID filters to one child only

B. ParentID should be in filter expression, not key condition

C. Missing a sort key in the table schema

D. ChildID cannot be used in KeyConditionExpression

Solution

1. Step 1: Analyze the key condition

   The condition ChildID = :cid restricts results to one specific child.

2. Step 2: Understand how to get all children

   To get all children, use a range operator like begins_with or omit ChildID condition.

3. Final Answer:

   Using '=' on ChildID filters to one child only -> Option A

4. Quick Check:

   Equals on sort key limits to one item [OK]

Hint: Use range operators, not '=' on sort key for multiple children [OK]

Common Mistakes:

• Thinking ParentID should be filter, not key condition
• Assuming ChildID can't be in key condition
• Ignoring sort key usage in queries

5. You want to model a company hierarchy in DynamoDB where each employee can have multiple subordinates. Which key design best supports efficient queries for all subordinates of a manager?

hard

A. Partition key: EmployeeID, Sort key: Timestamp

B. Partition key: EmployeeID, Sort key: ManagerID

C. Partition key: DepartmentID, Sort key: EmployeeID

D. Partition key: ManagerID, Sort key: EmployeeID

Solution

1. Step 1: Identify parent-child relationship

   Manager is the parent; employees are children (subordinates).

2. Step 2: Choose keys to group subordinates

   Using ManagerID as partition key groups all subordinates under one manager.

3. Step 3: Use EmployeeID as sort key

   Sort key EmployeeID uniquely identifies each subordinate under the manager.

4. Final Answer:

   Partition key: ManagerID, Sort key: EmployeeID -> Option D

5. Quick Check:

   Parent key = ManagerID groups subordinates [OK]

Hint: Partition by parent (manager) to group children (employees) [OK]

Common Mistakes:

• Using EmployeeID as partition key breaks grouping
• Using DepartmentID ignores manager-subordinate relation
• Using timestamp unrelated to hierarchy