One-to-many relationship patterns in DynamoDB

Introduction

One-to-many relationships help you connect one item to many related items in your database. This lets you organize data like a list of orders for one customer.

You want to store all orders made by a single customer.

You need to keep track of all comments on a blog post.

You want to list all products in a category.

You want to save all messages in a chat room.

You want to group all tasks under one project.

Syntax

DynamoDB

PartitionKey: unique ID for the main item (e.g., CustomerID)
SortKey: unique ID for each related item (e.g., OrderID)
Attributes: other details about each item

Use the PartitionKey to group related items together.

Use the SortKey to order or identify each related item uniquely.

Examples

This stores an order for customer 123 with order ID 001.

DynamoDB

PartitionKey: CUSTOMER#123
SortKey: ORDER#001
Attributes: {OrderDate: '2024-06-01', Total: 50}

This stores another order for the same customer with a different order ID.

DynamoDB

PartitionKey: CUSTOMER#123
SortKey: ORDER#002
Attributes: {OrderDate: '2024-06-02', Total: 75}

This stores a comment on a blog post with ID 456.

DynamoDB

PartitionKey: POST#456
SortKey: COMMENT#001
Attributes: {User: 'Alice', Text: 'Nice post!'}

Sample Program

This example creates a table to store orders. It inserts two orders for one customer and then retrieves all orders for that customer.

DynamoDB

CREATE TABLE Orders (
  PartitionKey STRING,
  SortKey STRING,
  OrderDate STRING,
  Total NUMBER,
  PRIMARY KEY (PartitionKey, SortKey)
);

-- Insert two orders for customer 123
INSERT INTO Orders (PartitionKey, SortKey, OrderDate, Total) VALUES ('CUSTOMER#123', 'ORDER#001', '2024-06-01', 50);
INSERT INTO Orders (PartitionKey, SortKey, OrderDate, Total) VALUES ('CUSTOMER#123', 'ORDER#002', '2024-06-02', 75);

-- Query all orders for customer 123
SELECT SortKey, OrderDate, Total FROM Orders WHERE PartitionKey = 'CUSTOMER#123' ORDER BY SortKey;

Important Notes

Use clear prefixes like CUSTOMER# or ORDER# to keep keys readable and organized.

Sorting by SortKey helps you get related items in order, like by date or ID.

Remember DynamoDB tables need a PartitionKey and SortKey for one-to-many patterns.

Summary

One-to-many relationships group many related items under one main item using PartitionKey and SortKey.

Use PartitionKey to identify the main item and SortKey to identify each related item.

This pattern helps organize data like orders per customer or comments per post.