AWS DynamoDB Single Table Design: Complete Architecture Guide

Single Table Design (STD) is the most controversial and misunderstood DynamoDB pattern. The premise: store all entity types in one table, use generic partition and sort keys, and leverage Composite Primary Keys and GSIs for access patterns. When done correctly, STD eliminates N+1 queries, reduces costs, and simplifies IAM. When done incorrectly, it creates debugging nightmares. This comprehensive guide provides the mental models, patterns, and anti-patterns you need to succeed.

Why Single Table Design?

In relational databases, we normalize data and use JOINs. DynamoDB has no JOINs. If you model one table per entity, fetching a customer with their orders requires:

Query CustomerTable: 1 request
Query OrderTable: 1 request
(For each order) Query OrderItemTable: N requests
Total: 2 + N requests

With Single Table Design:

Query SingleTable WHERE PK = "CUSTOMER#123": 1 request
Returns: Customer + All Orders + All Order Items

The Item Collection Model

Single Table Design exploits DynamoDB’s core feature: items with the same Partition Key form an “Item Collection” that is stored together and retrieved together.

flowchart TB
    subgraph Partition ["Partition: CUSTOMER#123"]
        A["SK: METADATA | Customer Info"]
        B["SK: ORDER#001 | Order Data"]
        C["SK: ORDER#001#ITEM#1 | Item 1"]
        D["SK: ORDER#001#ITEM#2 | Item 2"]
        E["SK: ORDER#002 | Order Data"]
    end
    
    Query["Query PK=CUSTOMER#123"] --> Partition
    
    style A fill:#E1F5FE,stroke:#0277BD
    style B fill:#C8E6C9,stroke:#2E7D32

Designing the Schema

Step 1: List Access Patterns

Before touching DynamoDB, document every access pattern your application needs:

Step 2: Design Primary Key

The generic pattern uses:

• PK: Entity type + Entity ID (e.g., CUSTOMER#123)
• SK: Entity type + optional hierarchy (e.g., ORDER#456)

PK                  | SK                    | Type     | Data
--------------------|----------------------|----------|------------------
CUSTOMER#123        | METADATA             | Customer | {name, email}
CUSTOMER#123        | ORDER#456            | Order    | {total, status}
CUSTOMER#123        | ORDER#456#ITEM#1     | OrderItem| {productId, qty}
CUSTOMER#123        | ORDER#456#ITEM#2     | OrderItem| {productId, qty}
CUSTOMER#123        | ORDER#789            | Order    | {total, status}
ORDER#456           | METADATA             | Order    | {customerId, total}
PRODUCT#AAA         | METADATA             | Product  | {name, price}

Notice: Orders are duplicated—once under the customer (for “get customer orders”) and once under their own PK (for “get order by ID”). This denormalization is intentional.

Step 3: Design GSIs for Additional Access Patterns

Access pattern #4 (“Get orders by status”) requires a GSI:

GSI1PK              | GSI1SK                | Data
--------------------|----------------------|------------------
STATUS#PENDING      | 2022-05-16#ORDER#456 | {customerId, total}
STATUS#SHIPPED      | 2022-05-15#ORDER#123 | {customerId, total}

Implementation with AWS SDK

// Get customer with all orders (single query)
public async Task<CustomerWithOrders> GetCustomerWithOrdersAsync(string customerId)
{
    var response = await _dynamoDb.QueryAsync(new QueryRequest
    {
        TableName = "SingleTable",
        KeyConditionExpression = "PK = :pk",
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            [":pk"] = new AttributeValue { S = $"CUSTOMER#{customerId}" }
        }
    });
    
    var customer = response.Items
        .First(i => i["SK"].S == "METADATA")
        .ToCustomer();
        
    var orders = response.Items
        .Where(i => i["SK"].S.StartsWith("ORDER#") && !i["SK"].S.Contains("#ITEM#"))
        .Select(i => i.ToOrder())
        .ToList();
        
    return new CustomerWithOrders { Customer = customer, Orders = orders };
}

// Get orders by status (uses GSI)
public async Task<List<Order>> GetOrdersByStatusAsync(string status)
{
    var response = await _dynamoDb.QueryAsync(new QueryRequest
    {
        TableName = "SingleTable",
        IndexName = "GSI1",
        KeyConditionExpression = "GSI1PK = :status",
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            [":status"] = new AttributeValue { S = $"STATUS#{status}" }
        }
    });
    
    return response.Items.Select(i => i.ToOrder()).ToList();
}

Anti-Patterns to Avoid

• Hot Partitions: Don’t use low-cardinality PKs like STATUS#PENDING as the main table PK—use GSIs instead
• Unbounded Collections: If a customer can have 1 million orders, the item collection exceeds 10GB. Paginate or use different PKs
• Write Amplification: Excessive denormalization means updating one entity requires updating many items

Key Takeaways

• Document all access patterns before designing schema
• Use composite keys: PK = EntityType#ID, SK = hierarchy
• Duplicate data intentionally to support multiple access patterns
• Use GSIs for secondary access patterns
• Avoid hot partitions and unbounded collections

References

• Alex DeBrie: The What, Why, and When of Single-Table Design