How to Model Hierarchical Data in DynamoDB: Simple Guide

To model hierarchical data in DynamoDB, use an adjacency list pattern where each item stores a reference to its parent. You can implement this with a Partition Key for the root and a Sort Key to represent child relationships, enabling efficient queries of parent-child hierarchies.

Syntax

Hierarchical data in DynamoDB is often modeled using an adjacency list pattern with a composite primary key:

• Partition Key (PK): Identifies the root or group of related items.
• Sort Key (SK): Represents the hierarchy level or child relationship, often combining parent and child IDs.

This structure allows querying all children of a parent by filtering on the PK and sorting by SK.

sql

CREATE TABLE Hierarchy (
  PK STRING,
  SK STRING,
  Data STRING,
  PRIMARY KEY (PK, SK)
);

Example

This example shows how to store a simple folder structure where each folder knows its parent. The PK is the root folder ID, and the SK encodes the path to each child.

plaintext

Items:
{
  PK: "root",
  SK: "folder#root",
  Data: "Root Folder"
}
{
  PK: "root",
  SK: "folder#root#child1",
  Data: "Child Folder 1"
}
{
  PK: "root",
  SK: "folder#root#child1#child2",
  Data: "Child Folder 2"
}

-- Query to get all children of root:
SELECT * FROM Hierarchy WHERE PK = 'root' AND SK BETWEEN 'folder#root#' AND 'folder#root#\uffff';

Output

Result:
PK: root, SK: folder#root, Data: Root Folder
PK: root, SK: folder#root#child1, Data: Child Folder 1
PK: root, SK: folder#root#child1#child2, Data: Child Folder 2

Common Pitfalls

• Not using composite keys: Without a Partition Key and Sort Key, queries for children become inefficient.
• Deep nesting: Very deep hierarchies can cause complex Sort Key values that are hard to manage.
• Missing indexes: Forgetting to create secondary indexes can limit query flexibility.

Always plan your keys to support your query patterns.

plaintext

Wrong approach:
{
  PK: "child1",
  SK: "",
  Data: "Child Folder 1"
}

Right approach:
{
  PK: "root",
  SK: "folder#root#child1",
  Data: "Child Folder 1"
}

Quick Reference

Concept	Description
Partition Key (PK)	Groups related items, usually the root or top-level entity
Sort Key (SK)	Defines the hierarchy path or child relationships
Adjacency List	Each item stores reference to its parent via keys
Query Pattern	Query by PK and filter or range on SK to get children
Secondary Index	Optional for alternative query patterns

Key Takeaways

Use composite primary keys with Partition Key and Sort Key to model hierarchy.

Store parent-child relationships in the Sort Key for efficient queries.

Avoid deep nesting that complicates Sort Key management.

Plan your access patterns before designing keys.

Consider secondary indexes for flexible queries.