Hierarchical data modeling in DynamoDB

Introduction

Hierarchical data modeling helps organize data that has parent-child relationships, like folders and files, in a way that is easy to find and use.

When you want to store categories and subcategories, like product types and their details.

When you need to represent organizational charts with managers and employees.

When you want to keep track of comments and replies in a discussion thread.

When you want to model file systems with folders and files inside them.

Syntax

DynamoDB

Use a primary key (partition key) for the top-level item.
Use a sort key to represent the hierarchy, often by combining parent and child IDs or using a path-like string.
Example:
PartitionKey: 'Category#1'
SortKey: 'SubCategory#1'

In DynamoDB, you often use a composite key (partition key + sort key) to model hierarchy.

You can use special characters like '#' to separate levels in the sort key for clarity.

Examples

Stores a file inside folder A.

DynamoDB

PartitionKey: 'Folder#A'
SortKey: 'File#1'

Represents a team inside a department inside an organization.

DynamoDB

PartitionKey: 'Org#123'
SortKey: 'Dept#456#Team#789'

Stores a comment under a post.

DynamoDB

PartitionKey: 'Post#100'
SortKey: 'Comment#200'

Sample Program

This example creates a table with a partition key and sort key to store folders and files. It inserts one folder and two files inside it. Then it queries all items inside the folder.

DynamoDB

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

-- Insert a folder
INSERT INTO Hierarchy (PK, SK, Data) VALUES ('Folder#A', 'Metadata', 'Folder A info');

-- Insert files inside folder
INSERT INTO Hierarchy (PK, SK, Data) VALUES ('Folder#A', 'File#1', 'File 1 content');
INSERT INTO Hierarchy (PK, SK, Data) VALUES ('Folder#A', 'File#2', 'File 2 content');

-- Query all items in Folder A
SELECT * FROM Hierarchy WHERE PK = 'Folder#A';

Important Notes

DynamoDB does not support joins, so hierarchical data is often stored in a single table with composite keys.

Use consistent key naming patterns to make querying easier.

You can query all children of a parent by filtering on the partition key.

Summary

Hierarchical data modeling organizes data with parent-child relationships.

Use partition key for the parent and sort key for children in DynamoDB.

Consistent key patterns help retrieve related items efficiently.