DynamoDB single-table design isn’t about "joining" data like SQL; it’s about querying for related items in a single, optimized request.
Let’s see this in action. Imagine we’re modeling users and their orders. A common SQL approach would be two tables: users and orders, with a foreign key from orders to users. In DynamoDB single-table design, we’ll put both into one table, using a composite primary key to define relationships.
Here’s a sample table definition in AWS CLI:
aws dynamodb create-table --table-name MySingleTable \
--attribute-definitions \
AttributeName=PK,AttributeType=S \
AttributeName=SK,AttributeType=S \
--key-schema \
AttributeName=PK,KeyType=HASH \
AttributeName=SK,KeyType=RANGE \
--provisioned-throughput \
ReadCapacityUnits=5,WriteCapacityUnits=5
Our PK (Partition Key) and SK (Sort Key) will be used creatively. For a user, we might have PK: USER#<userId> and SK: METADATA. For an order, PK: USER#<userId> and SK: ORDER#<orderId>. Notice how both the user and their orders share the same PK.
Now, how do we fetch a user and all their orders? We use a Query operation.
{
"TableName": "MySingleTable",
"KeyConditionExpression": "PK = :pk AND begins_with(SK, :sk)",
"ExpressionAttributeValues": {
":pk": {"S": "USER#123"},
":sk": {"S": "ORDER#"}
}
}
This query targets PK "USER#123" and then uses begins_with(SK, "ORDER#") to retrieve all items whose SK starts with "ORDER#". This effectively fetches all orders for user "123" in a single API call. If we wanted the user’s metadata too, we’d adjust the SK condition or perform a second, targeted query.
The real power comes from defining different item types and their relationships within this single table. We can model "has-a" (like a user having orders) and "many-to-many" relationships using different PK/SK patterns. For example, to link users to groups, we might have:
- User Item:
PK: USER#<userId>,SK: METADATA - Group Item:
PK: GROUP#<groupId>,SK: METADATA - User-Group Membership:
PK: USER#<userId>,SK: GROUP#<groupId> - Group-User Membership:
PK: GROUP#<groupId>,SK: USER#<userId>
To get all groups a user belongs to, you’d query PK = USER#<userId> and begins_with(SK, GROUP#). To get all users in a group, you’d query PK = GROUP#<groupId> and begins_with(SK, USER#). This pattern is incredibly efficient as it leverages DynamoDB’s native query capabilities without needing any application-level joins.
The mental model is not a collection of tables, but a single, highly structured data graph. The PK defines the "root" entity or a specific relationship, and the SK defines the "path" or type of related entity. By carefully crafting these keys, you can model complex relationships and retrieve them with minimal, high-performance DynamoDB operations.
What most people don’t realize is the subtle but critical role of the SK in defining the order of items within a partition. When you query with begins_with, between, or a simple equality on the SK, DynamoDB returns the results sorted according to the SK’s string value. This is fundamental for retrieving hierarchical data or time-series information efficiently.
The next step is understanding how to use Global Secondary Indexes (GSIs) and Local Secondary Indexes (LSIs) to query across different access patterns that don’t fit the primary key structure.