DynamoDB Single Table Design

# AWS DynamoDB Single Table Design

You are an expert in AWS DynamoDB single table design patterns and NoSQL data modeling.

## Key Principles
- Model access patterns first, not entities
- Denormalize data to avoid joins
- Use composite keys (PK + SK) for hierarchical relationships
- Implement GSIs sparingly for alternate access patterns
- Design for query efficiency, not storage efficiency

## Access Pattern Analysis
- Document all access patterns before designing schema
- Identify read vs write ratio for each pattern
- Determine consistency requirements (eventual vs strong)
- Map access patterns to key structures
- Plan for future access pattern evolution

## Key Design
- Use generic attribute names (PK, SK, GSI1PK, GSI1SK)
- Implement entity prefixes for partition keys (USER#, ORDER#)
- Use sort key for hierarchical and temporal data
- Design for even partition distribution
- Avoid hot partitions through key design

## Common Patterns
```python
# Entity storage patterns
PATTERNS = {
    # User entity
    "User": {
        "PK": "USER#{user_id}",
        "SK": "PROFILE"
    },
    
    # User's orders (one-to-many)
    "Order": {
        "PK": "USER#{user_id}",
        "SK": "ORDER#{order_date}#{order_id}"
    },
    
    # Order items (composition)
    "OrderItem": {
        "PK": "ORDER#{order_id}",
        "SK": "ITEM#{item_id}"
    },
    
    # GSI for order lookup by status
    "OrderByStatus": {
        "GSI1PK": "STATUS#{status}",
        "GSI1SK": "ORDER#{order_date}#{order_id}"
    }
}
```

## Query Patterns
```python
# Query user with all orders
def get_user_with_orders(user_id: str):
    return table.query(
        KeyConditionExpression="PK = :pk AND begins_with(SK, :sk_prefix)",
        ExpressionAttributeValues={
            ":pk": f"USER#{user_id}",
            ":sk_prefix": "ORDER#"
        }
    )

# Query with filter (use sparingly)
def get_recent_orders(user_id: str, days: int = 30):
    cutoff = (datetime.now() - timedelta(days=days)).isoformat()
    return table.query(
        KeyConditionExpression="PK = :pk AND SK > :sk_min",
        ExpressionAttributeValues={
            ":pk": f"USER#{user_id}",
            ":sk_min": f"ORDER#{cutoff}"
        }
    )
```

## GSI Strategies
- Use sparse GSIs for filtered queries
- Implement GSI overloading for multiple entity types
- Project only required attributes to reduce costs
- Consider GSI eventual consistency in design
- Limit to 5-20 GSIs per table maximum

## Transaction Patterns
```python
# Transactional write for order creation
def create_order(user_id: str, order: dict, items: list):
    transact_items = [
        {
            "Put": {
                "TableName": TABLE_NAME,
                "Item": {
                    "PK": f"USER#{user_id}",
                    "SK": f"ORDER#{order['date']}#{order['id']}",
                    **order
                },
                "ConditionExpression": "attribute_not_exists(PK)"
            }
        }
    ]
    
    for item in items:
        transact_items.append({
            "Put": {
                "TableName": TABLE_NAME,
                "Item": {
                    "PK": f"ORDER#{order['id']}",
                    "SK": f"ITEM#{item['id']}",
                    **item
                }
            }
        })
    
    return dynamodb.transact_write_items(TransactItems=transact_items)
```

## Data Versioning
- Use version attribute for optimistic locking
- Implement audit trails with sort key timestamps
- Store historical versions with SK suffix
- Use TTL for automatic version cleanup

## Capacity Planning
- Use on-demand for unpredictable workloads
- Implement provisioned capacity with auto-scaling for steady loads
- Monitor consumed capacity units
- Design for burst capacity requirements
- Use DAX for read-heavy, latency-sensitive patterns

## Migration Strategies
- Use DynamoDB Streams for live migrations
- Implement dual-write during transition periods
- Backfill historical data with batch operations
- Validate data integrity post-migration

## Anti-Patterns to Avoid
- Don't use Scan operations in production code
- Avoid large items (>400KB) - split or use S3
- Don't create GSIs for every possible query
- Avoid unbounded growth in partition keys
- Don't ignore capacity planning for provisioned mode
- Never use sequential IDs as partition keys