DynamoDB doesn’t actually have foreign keys, which forces you to think about data modeling in a completely new way for hierarchical data.
Let’s say you want to model a typical Customer -> Order -> OrderItem relationship. In a relational database, you’d have three tables with foreign keys linking them. In DynamoDB, you’ll typically consolidate this into a single table using a composite primary key (Partition Key and Sort Key) and a technique called "Adjacency List" or "Materialized Path."
Here’s how we can model this using a single ecommerce table.
{
"TableName": "ecommerce",
"KeySchema": [
{ "AttributeName": "PK", "KeyType": "HASH" },
{ "AttributeName": "SK", "KeyType": "RANGE" }
],
"AttributeDefinitions": [
{ "AttributeName": "PK", "AttributeType": "S" },
{ "AttributeName": "SK", "AttributeType": "S" }
],
"ProvisionedThroughput": {
"ReadCapacityUnits": 5,
"WriteCapacityUnits": 5
}
}
We’ll use PK (Partition Key) and SK (Sort Key) for our composite primary key.
Now, let’s imagine some data for a customer, their orders, and order items.
Customer:
{
"PK": "CUSTOMER#12345",
"SK": "METADATA",
"customer_id": "12345",
"name": "Alice Smith",
"email": "alice.smith@example.com"
}
Here, PK is CUSTOMER#12345 and SK is METADATA. This "METADATA" sort key is a common pattern to hold the primary attributes of the entity at the top of the hierarchy.
Order 1 for Customer 12345:
{
"PK": "CUSTOMER#12345",
"SK": "ORDER#98765",
"order_id": "98765",
"order_date": "2023-10-27T10:00:00Z",
"total_amount": 150.75
}
Notice how the PK is still CUSTOMER#12345. This is how we link the order back to the customer. The SK here is ORDER#98765.
OrderItem 1 for Order 98765:
{
"PK": "CUSTOMER#12345",
"SK": "ORDER#98765#ITEM#ABC",
"order_item_id": "ABC",
"product_name": "Wireless Mouse",
"quantity": 1,
"price": 25.00
}
The PK is still CUSTOMER#12345. The SK is now a composite of the order ID and the item ID: ORDER#98765#ITEM#ABC. This nesting allows us to group all items belonging to a specific order under the same customer partition.
OrderItem 2 for Order 98765:
{
"PK": "CUSTOMER#12345",
"SK": "ORDER#98765#ITEM#DEF",
"order_item_id": "DEF",
"product_name": "Mechanical Keyboard",
"quantity": 1,
"price": 125.75
}
This item also shares the same PK and has an SK that starts with ORDER#98765, allowing it to be sorted alongside the previous item.
The core idea is that the PK identifies the "root" entity (the customer in this case), and the SK encodes the relationship and the type of entity. By using prefixes like CUSTOMER#, ORDER#, and ITEM#, we can leverage DynamoDB’s sort key capabilities to query for related items.
To retrieve all orders for a customer, you’d use a Query operation on the ecommerce table:
- KeyConditionExpression:
PK = :pk - ExpressionAttributeValues:
{":pk": {"S": "CUSTOMER#12345"}} - FilterExpression (optional, for specific types):
begins_with(SK, :sk_prefix) - ExpressionAttributeValues (if using FilterExpression):
{":pk": {"S": "CUSTOMER#12345"}, ":sk_prefix": {"S": "ORDER#"}}
This query would return the customer’s metadata and all their orders.
To retrieve all order items for a specific order, you’d use a similar query:
- KeyConditionExpression:
PK = :pk AND begins_with(SK, :sk_prefix) - ExpressionAttributeValues:
{":pk": {"S": "CUSTOMER#12345"}, ":sk_prefix": {"S": "ORDER#98765#ITEM#"}}
This allows you to fetch a customer, their orders, and their order items efficiently. The "trick" here is that the SK is designed to allow for range queries (using begins_with or explicit ranges) to retrieve specific branches of your hierarchy.
The most surprising aspect of this pattern is how much data you can effectively "join" within a single table by carefully crafting your PK and SK. You’re not performing joins in the relational sense; you’re performing targeted queries that retrieve pre-associated data based on key structures.
The next hurdle you’ll likely face is how to handle queries that need to traverse up the hierarchy (e.g., finding all customers who ordered a specific product).