DynamoDB Single-Table Design: The Pattern That Scales to Billions of Items

// Multi-table design (wrong for DynamoDB):
// users table: { userId, name, email }
// orders table: { orderId, userId, total, date }
// items table: { itemId, orderId, product, qty }
// To get a user + their orders: 2 separate queries

// Single-table design (correct):
// Everything in ONE table with overloaded PK/SK:

// User record:
{ PK: "USER#u123", SK: "PROFILE", name: "Alice", email: "alice@co.com" }

// User's orders:
{ PK: "USER#u123", SK: "ORDER#2026-04-05#o456", total: 99.99, status: "shipped" }
{ PK: "USER#u123", SK: "ORDER#2026-03-20#o789", total: 45.00, status: "delivered" }

// Order's items:
{ PK: "ORDER#o456", SK: "ITEM#prod-001", product: "Laptop Stand", qty: 1 }
{ PK: "ORDER#o456", SK: "ITEM#prod-002", product: "USB Hub", qty: 2 }

// Get user profile + all orders in ONE query:
// PK = "USER#u123", SK begins_with "ORDER#" → all orders, sorted by date

// Example access patterns for an e-commerce system:
// 1. Get user profile by userId
// 2. Get all orders for a user (sorted newest first)
// 3. Get a specific order by orderId
// 4. Get all items in an order
// 5. Get all orders with status "pending"
// 6. Get user's most recent 10 orders

// Key design that satisfies all 6 patterns:
// Base table:
// PK SK EntityType ...attributes
// USER#u123 PROFILE USER name, email
// USER#u123 ORDER#2026-04-05 ORDER orderId, total, status
// ORDER#o456 ITEM#prod-001 ITEM product, qty
// ORDER#o456 METADATA ORDER userId, total, status

// GSI (Global Secondary Index) for pattern 5 (orders by status):
// GSI1PK GSI1SK
// STATUS#pending 2026-04-05 → all pending orders sortable by date

// Query pattern 1: GetItem PK=USER#u123, SK=PROFILE
// Query pattern 2: Query PK=USER#u123, SK begins_with ORDER#, ScanIndexForward=false
// Query pattern 5: Query on GSI1, GSI1PK=STATUS#pending

// AWS SDK v3 example — create and query with GSI
const { DynamoDBClient, QueryCommand, PutItemCommand } = require('@aws-sdk/client-dynamodb');
const { marshall, unmarshall } = require('@aws-sdk/util-dynamodb');

const client = new DynamoDBClient({ region: 'us-east-1' });

// Write an order with GSI attributes
await client.send(new PutItemCommand({
 TableName: 'CheatCodersStore',
 Item: marshall({
 PK: 'USER#u123',
 SK: 'ORDER#2026-04-05#o456',
 GSI1PK: 'STATUS#pending', // For orders-by-status pattern
 GSI1SK: '2026-04-05T10:30:00Z', // Sortable timestamp
 orderId: 'o456',
 userId: 'u123',
 total: 99.99,
 status: 'pending',
 EntityType: 'ORDER'
 })
}));

// Query all pending orders (GSI1)
const pendingOrders = await client.send(new QueryCommand({
 TableName: 'CheatCodersStore',
 IndexName: 'GSI1',
 KeyConditionExpression: 'GSI1PK = :status',
 ExpressionAttributeValues: marshall({ ':status': 'STATUS#pending' }),
 ScanIndexForward: false // Newest first
}));

const orders = pendingOrders.Items.map(unmarshall);
console.log(orders);

// Model deeply nested data: Organisation → Team → Member → Task

// All in one table:
{ PK: "ORG#acme", SK: "METADATA", name: "Acme Corp" }
{ PK: "ORG#acme", SK: "TEAM#engineering", teamName: "Engineering" }
{ PK: "ORG#acme", SK: "TEAM#engineering#MEMBER#u1", name: "Alice", role: "Lead" }
{ PK: "MEMBER#u1", SK: "TASK#2026-04-05#t001", title: "Fix bug", done: false }

// Get entire org structure (departments + members) in ONE query:
// PK = "ORG#acme", SK begins_with "" → returns everything

// Get just teams:
// PK = "ORG#acme", SK begins_with "TEAM#"

// Get a specific team's members:
// PK = "ORG#acme", SK begins_with "TEAM#engineering#MEMBER#"

// This is what makes DynamoDB magical for hierarchical data —
// the sort key encodes the hierarchy, enabling partial queries at any level

// ❌ MISTAKE 1: Using sequential IDs as partition keys
// This creates "hot partitions" — all traffic hits one server
{ PK: "1", SK: "ORDER" } // IDs 1,2,3 all likely on same partition
// ✅ FIX: Use random UUIDs or ULIDs (time-sortable random IDs)
{ PK: "USER#01HVZK2X...", SK: "ORDER#..." } // Distributed across partitions

// ❌ MISTAKE 2: Putting filter conditions in FilterExpression instead of keys
// FilterExpression reads ALL items then filters — you still pay for reads
const wrong = await client.send(new QueryCommand({
 FilterExpression: 'userId = :uid', // Scans everything!
 ...
}));
// ✅ FIX: Design keys so filters are on PK/SK (KeyConditionExpression)

// ❌ MISTAKE 3: Scan operations in production
// Scan reads EVERY item in the table — avoid completely
// ✅ FIX: Every access pattern should use Query on base table or GSI