DynamoDB Data Modeling: Single-Table Design Patterns

DynamoDB is not a relational database, and treating it like one leads to poor performance and high costs. Effective DynamoDB design starts with your access patterns, not your data structure. This guide walks through single-table design patterns that let you serve complex queries with minimal read capacity.

Think Access Patterns First

In relational databases, you normalize your data and figure out queries later. In DynamoDB, you do the opposite. List every access pattern your application needs, then design your key schema to serve them.

Consider an e-commerce application with these access patterns:

1. Get a customer by ID
2. Get all orders for a customer
3. Get a specific order by ID
4. Get all items in an order
5. Get all orders in a date range
6. Get orders by status

Each of these must map to either a GetItem, Query, or at most a Query on a Global Secondary Index.

Single-Table Design Fundamentals

Single-table design places multiple entity types in the same table, using generic key names like PK and SK to represent different access patterns.

Setting Up the Table

aws dynamodb create-table \
  --table-name ECommerceTable \
  --attribute-definitions \
    AttributeName=PK,AttributeType=S \
    AttributeName=SK,AttributeType=S \
    AttributeName=GSI1PK,AttributeType=S \
    AttributeName=GSI1SK,AttributeType=S \
  --key-schema \
    AttributeName=PK,KeyType=HASH \
    AttributeName=SK,KeyType=RANGE \
  --global-secondary-indexes '[
    {
      "IndexName": "GSI1",
      "KeySchema": [
        {"AttributeName": "GSI1PK", "KeyType": "HASH"},
        {"AttributeName": "GSI1SK", "KeyType": "RANGE"}
      ],
      "Projection": {"ProjectionType": "ALL"},
      "ProvisionedThroughput": {
        "ReadCapacityUnits": 5,
        "WriteCapacityUnits": 5
      }
    }
  ]' \
  --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

Entity Design

Here is how multiple entities map to the same table:

Entity	PK	SK	GSI1PK	GSI1SK
Customer	CUST#123	CUST#123	-	-
Order	CUST#123	ORDER#2026-07-06#001	ORDER#001	STATUS#shipped
Item	ORDER#001	ITEM#SKU-ABC	PROD#SKU-ABC	ORDER#001

Writing Entities

import boto3
from datetime import datetime

dynamodb = boto3.resource('dynamodb')
table = dynamodb.Table('ECommerceTable')

# Create a customer
table.put_item(Item={
    'PK': 'CUST#123',
    'SK': 'CUST#123',
    'entity_type': 'Customer',
    'name': 'Jane Smith',
    'email': 'jane@example.com',
    'created_at': '2026-01-15T10:30:00Z'
})

# Create an order (stored under customer partition)
table.put_item(Item={
    'PK': 'CUST#123',
    'SK': 'ORDER#2026-07-06#001',
    'GSI1PK': 'ORDER#001',
    'GSI1SK': 'STATUS#pending',
    'entity_type': 'Order',
    'order_id': '001',
    'total': 149.99,
    'status': 'pending',
    'created_at': '2026-07-06T14:00:00Z'
})

# Create order items (stored under order partition)
table.put_item(Item={
    'PK': 'ORDER#001',
    'SK': 'ITEM#SKU-ABC',
    'GSI1PK': 'PROD#SKU-ABC',
    'GSI1SK': 'ORDER#001',
    'entity_type': 'OrderItem',
    'product_name': 'Wireless Headphones',
    'quantity': 1,
    'price': 99.99
})

Querying Access Patterns

Get Customer by ID (Pattern 1)

response = table.get_item(Key={
    'PK': 'CUST#123',
    'SK': 'CUST#123'
})
customer = response['Item']

Get All Orders for a Customer (Pattern 2)

Since orders are stored under the customer’s partition key with a sort key prefix of ORDER#, a single query retrieves them all.

from boto3.dynamodb.conditions import Key

response = table.query(
    KeyConditionExpression=Key('PK').eq('CUST#123') & Key('SK').begins_with('ORDER#')
)
orders = response['Items']

Get Orders in a Date Range (Pattern 5)

Because the sort key includes the date (ORDER#2026-07-06#001), you can query for orders within a date range.

response = table.query(
    KeyConditionExpression=(
        Key('PK').eq('CUST#123') &
        Key('SK').between('ORDER#2026-07-01', 'ORDER#2026-07-31')
    )
)
july_orders = response['Items']

Get Orders by Status (Pattern 6)

This uses GSI1 where the sort key contains the status.

response = table.query(
    IndexName='GSI1',
    KeyConditionExpression=(
        Key('GSI1PK').eq('ORDER#001') &
        Key('GSI1SK').begins_with('STATUS#')
    )
)

Composite Sort Keys

Composite sort keys let you create hierarchical data that supports multiple query granularities. This is especially useful for location data, time-series data, and organizational hierarchies.

# Store sales data with composite sort key: REGION#COUNTRY#STATE#CITY
items = [
    {
        'PK': 'SALES#2026',
        'SK': 'NA#US#CA#SanFrancisco',
        'revenue': 50000,
        'units': 120
    },
    {
        'PK': 'SALES#2026',
        'SK': 'NA#US#CA#LosAngeles',
        'revenue': 75000,
        'units': 200
    },
    {
        'PK': 'SALES#2026',
        'SK': 'NA#US#NY#NewYork',
        'revenue': 120000,
        'units': 350
    },
    {
        'PK': 'SALES#2026',
        'SK': 'EU#DE#BY#Munich',
        'revenue': 45000,
        'units': 90
    }
]

# Query all North American sales
response = table.query(
    KeyConditionExpression=Key('PK').eq('SALES#2026') & Key('SK').begins_with('NA#')
)

# Query all California sales
response = table.query(
    KeyConditionExpression=Key('PK').eq('SALES#2026') & Key('SK').begins_with('NA#US#CA#')
)

Many-to-Many Relationships

Many-to-many relationships require an adjacency list pattern. Consider students enrolled in courses.

# Student enrolled in a course (stored twice for bidirectional access)
def enroll_student(student_id, course_id, student_name, course_name):
    table.put_item(Item={
        'PK': f'STUDENT#{student_id}',
        'SK': f'COURSE#{course_id}',
        'entity_type': 'Enrollment',
        'student_name': student_name,
        'course_name': course_name,
        'enrolled_at': datetime.utcnow().isoformat()
    })
    table.put_item(Item={
        'PK': f'COURSE#{course_id}',
        'SK': f'STUDENT#{student_id}',
        'entity_type': 'Enrollment',
        'student_name': student_name,
        'course_name': course_name,
        'enrolled_at': datetime.utcnow().isoformat()
    })

# Get all courses for a student
response = table.query(
    KeyConditionExpression=Key('PK').eq('STUDENT#42') & Key('SK').begins_with('COURSE#')
)

# Get all students in a course
response = table.query(
    KeyConditionExpression=Key('PK').eq('COURSE#CS101') & Key('SK').begins_with('STUDENT#')
)

Use DynamoDB transactions to ensure both items are written atomically.

dynamodb_client = boto3.client('dynamodb')

dynamodb_client.transact_write_items(
    TransactItems=[
        {
            'Put': {
                'TableName': 'ECommerceTable',
                'Item': {
                    'PK': {'S': 'STUDENT#42'},
                    'SK': {'S': 'COURSE#CS101'},
                    'entity_type': {'S': 'Enrollment'}
                }
            }
        },
        {
            'Put': {
                'TableName': 'ECommerceTable',
                'Item': {
                    'PK': {'S': 'COURSE#CS101'},
                    'SK': {'S': 'STUDENT#42'},
                    'entity_type': {'S': 'Enrollment'}
                }
            }
        }
    ]
)

GSI Overloading

A single GSI can serve multiple access patterns by storing different entity types with different key meanings. This is called GSI overloading.

# Product entity: GSI1PK = category, GSI1SK = price
table.put_item(Item={
    'PK': 'PROD#SKU-ABC',
    'SK': 'PROD#SKU-ABC',
    'GSI1PK': 'CAT#electronics',
    'GSI1SK': 'PRICE#00099.99',
    'entity_type': 'Product',
    'name': 'Wireless Headphones'
})

# Employee entity: GSI1PK = department, GSI1SK = hire date
table.put_item(Item={
    'PK': 'EMP#1001',
    'SK': 'EMP#1001',
    'GSI1PK': 'DEPT#engineering',
    'GSI1SK': 'HIRED#2024-03-15',
    'entity_type': 'Employee',
    'name': 'Alice Johnson'
})

# Query products in a category sorted by price
response = table.query(
    IndexName='GSI1',
    KeyConditionExpression=(
        Key('GSI1PK').eq('CAT#electronics') &
        Key('GSI1SK').begins_with('PRICE#')
    )
)

# Query employees in engineering sorted by hire date
response = table.query(
    IndexName='GSI1',
    KeyConditionExpression=(
        Key('GSI1PK').eq('DEPT#engineering') &
        Key('GSI1SK').begins_with('HIRED#')
    )
)

Handling Hot Partitions

If a single partition key receives disproportionate traffic, you get throttled. Write sharding distributes the load.

import random

SHARD_COUNT = 10

def write_event(event_data):
    shard = random.randint(0, SHARD_COUNT - 1)
    table.put_item(Item={
        'PK': f'EVENTS#2026-07-06#SHARD{shard}',
        'SK': f'TS#{datetime.utcnow().isoformat()}',
        **event_data
    })

def read_all_events(date):
    all_items = []
    for shard in range(SHARD_COUNT):
        response = table.query(
            KeyConditionExpression=Key('PK').eq(f'EVENTS#{date}#SHARD{shard}')
        )
        all_items.extend(response['Items'])
    return sorted(all_items, key=lambda x: x['SK'])

CloudFormation Template

Here is a complete CloudFormation template for a single-table design with two GSIs.

AWSTemplateFormatVersion: '2010-09-09'
Description: DynamoDB single-table design

Resources:
  SingleTable:
    Type: AWS::DynamoDB::Table
    Properties:
      TableName: AppTable
      BillingMode: PAY_PER_REQUEST
      AttributeDefinitions:
        - AttributeName: PK
          AttributeType: S
        - AttributeName: SK
          AttributeType: S
        - AttributeName: GSI1PK
          AttributeType: S
        - AttributeName: GSI1SK
          AttributeType: S
        - AttributeName: GSI2PK
          AttributeType: S
        - AttributeName: GSI2SK
          AttributeType: S
      KeySchema:
        - AttributeName: PK
          KeyType: HASH
        - AttributeName: SK
          KeyType: RANGE
      GlobalSecondaryIndexes:
        - IndexName: GSI1
          KeySchema:
            - AttributeName: GSI1PK
              KeyType: HASH
            - AttributeName: GSI1SK
              KeyType: RANGE
          Projection:
            ProjectionType: ALL
        - IndexName: GSI2
          KeySchema:
            - AttributeName: GSI2PK
              KeyType: HASH
            - AttributeName: GSI2SK
              KeyType: RANGE
          Projection:
            ProjectionType: ALL
      PointInTimeRecoverySpecification:
        PointInTimeRecoveryEnabled: true
      SSESpecification:
        SSEEnabled: true
      Tags:
        - Key: Environment
          Value: production

Wrapping Up

DynamoDB data modeling is a different discipline from relational design. Start by enumerating your access patterns, then design your key schema to serve them with single-table design. Use composite sort keys for hierarchical queries, GSI overloading to minimize index count, adjacency lists for many-to-many relationships, and write sharding for hot partitions. The upfront design effort pays off with single-digit millisecond reads at any scale, predictable costs, and zero operational overhead for scaling.