How to Model One to Many Relationships in DynamoDB

In DynamoDB, model one to many relationships by using a single partition key for the 'one' side and different sort keys for the 'many' side items within the same partition. This groups related items together, allowing efficient queries with Query operations on the partition key.

Syntax

To model one to many in DynamoDB, use a table with a composite primary key: a Partition Key representing the 'one' entity, and a Sort Key to distinguish each 'many' related item.

This structure lets you store multiple related items under the same partition key but with unique sort keys.

sql

CREATE TABLE ExampleTable (
  PartitionKey STRING,
  SortKey STRING,
  Attribute1 STRING,
  Attribute2 NUMBER,
  PRIMARY KEY (PartitionKey, SortKey)
);

Example

This example shows a DynamoDB table storing a blog author and their multiple blog posts. The author's ID is the partition key, and each post has a unique sort key.

json

Table: Blog

Items:
{
  "PartitionKey": "author#123",
  "SortKey": "profile",
  "Name": "Alice",
  "Email": "[e-mail]"
}

{
  "PartitionKey": "author#123",
  "SortKey": "post#1",
  "Title": "My First Post",
  "Content": "Hello world!"
}

{
  "PartitionKey": "author#123",
  "SortKey": "post#2",
  "Title": "Another Post",
  "Content": "More content here."
}

-- Query to get all posts by author 123
Query(
  TableName="Blog",
  KeyConditionExpression="PartitionKey = :authorId AND begins_with(SortKey, :postPrefix)",
  ExpressionAttributeValues={
    ":authorId": "author#123",
    ":postPrefix": "post#"
  }
)

Output

[
  {
    "PartitionKey": "author#123",
    "SortKey": "post#1",
    "Title": "My First Post",
    "Content": "Hello world!"
  },
  {
    "PartitionKey": "author#123",
    "SortKey": "post#2",
    "Title": "Another Post",
    "Content": "More content here."
  }
]

Common Pitfalls

• Using separate partition keys for related items causes inefficient queries because you must scan multiple partitions.
• Not using a sort key to distinguish 'many' items leads to overwriting data.
• Trying to model one to many with multiple tables increases complexity and reduces performance.

Correct approach: Use one partition key for the 'one' entity and unique sort keys for each 'many' item.

json

/* Wrong: Separate partition keys for posts */
{
  "PartitionKey": "post#1",
  "SortKey": "",
  "Title": "My First Post"
}

/* Right: Same partition key, different sort keys */
{
  "PartitionKey": "author#123",
  "SortKey": "post#1",
  "Title": "My First Post"
}

Quick Reference

Concept	Description
Partition Key	Identifies the 'one' entity in the relationship.
Sort Key	Distinguishes each 'many' related item under the same partition.
Query Operation	Fetches all related 'many' items efficiently by partition key.
Item Collection	All items with the same partition key form a collection.
Avoid Scans	Do not use scans; use queries with partition and sort keys.

Key Takeaways

Use a composite primary key with partition key for the 'one' and sort key for the 'many'.

Store all related items under the same partition key to enable efficient queries.

Use sort key prefixes to filter and organize related items.

Avoid separate partition keys for related items to prevent costly scans.

Query by partition key and sort key conditions to retrieve one-to-many data.