designing-dynamodb-tables

What this skill does

# Designing DynamoDB Tables

You are an expert in designing production-ready DynamoDB single-table schemas optimized for performance, cost, and scalability.

## Core Principle: Single-Table Design

ONE table per application. Always. No exceptions.

### Why Single-Table?
- Reduces cross-table joins (impossible in DynamoDB anyway)
- Minimizes costs (fewer tables, consolidated throughput)
- Simplifies queries (related data co-located)
- Better performance (fetch multiple entity types in one query)

## Table Structure

### Primary Key
```
Partition Key (PK): STRING
Sort Key (SK): STRING
```

Always use generic names `PK` and `SK`. This allows flexibility for any entity type.

### Attributes
```
PK           STRING   (Partition Key)
SK           STRING   (Sort Key)
EntityType   STRING   (e.g., "User", "Post", "Comment")
GSI1PK       STRING   (GSI #1 Partition Key)
GSI1SK       STRING   (GSI #1 Sort Key)
GSI2PK       STRING   (GSI #2 Partition Key) [optional]
GSI2SK       STRING   (GSI #2 Sort Key) [optional]
...entity-specific attributes...
CreatedAt    STRING   (ISO 8601 timestamp)
UpdatedAt    STRING   (ISO 8601 timestamp)
```

## Entity Patterns

### User Entity

**Access Patterns:**
1. Get user by ID
2. Get user by email
3. List all users (admin only, paginated)

**Design:**
```
User Item:
  PK: USER#<userId>
  SK: PROFILE
  EntityType: User
  GSI1PK: USER#<email>
  GSI1SK: USER#<email>
  Email: [email protected]
  Name: John Doe
  CreatedAt: 2024-01-01T00:00:00Z
  UpdatedAt: 2024-01-01T00:00:00Z
```

**Queries:**
```typescript
// Get user by ID
const user = await docClient.get({
  TableName: TABLE_NAME,
  Key: {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
  },
});

// Get user by email (using GSI1)
const user = await docClient.query({
  TableName: TABLE_NAME,
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :email',
  ExpressionAttributeValues: {
    ':email': `USER#${email}`,
  },
});
```

### One-to-Many Relationships

**Example: User has many Posts**

**Access Patterns:**
1. Get all posts by a user
2. Get a specific post by ID
3. Get recent posts (all users, paginated)

**Design:**
```
User Item:
  PK: USER#<userId>
  SK: PROFILE
  EntityType: User
  ...

Post Item:
  PK: USER#<userId>
  SK: POST#<timestamp>#<postId>
  EntityType: Post
  GSI1PK: POST#<postId>
  GSI1SK: POST#<postId>
  GSI2PK: ALL_POSTS
  GSI2SK: POST#<timestamp>
  PostId: <postId>
  Title: Post title
  Content: Post content
  CreatedAt: 2024-01-01T00:00:00Z
```

**Queries:**
```typescript
// Get all posts by user (sorted by timestamp, newest first)
const posts = await docClient.query({
  TableName: TABLE_NAME,
  KeyConditionExpression: 'PK = :userId AND begins_with(SK, :prefix)',
  ExpressionAttributeValues: {
    ':userId': `USER#${userId}`,
    ':prefix': 'POST#',
  },
  ScanIndexForward: false, // Descending order
});

// Get specific post by ID (using GSI1)
const post = await docClient.query({
  TableName: TABLE_NAME,
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :postId',
  ExpressionAttributeValues: {
    ':postId': `POST#${postId}`,
  },
});

// Get recent posts from all users (using GSI2)
const posts = await docClient.query({
  TableName: TABLE_NAME,
  IndexName: 'GSI2',
  KeyConditionExpression: 'GSI2PK = :allPosts',
  ExpressionAttributeValues: {
    ':allPosts': 'ALL_POSTS',
  },
  ScanIndexForward: false,
  Limit: 20,
});
```

### Many-to-Many Relationships

**Example: Users can like many Posts, Posts can be liked by many Users**

**Access Patterns:**
1. Get all posts liked by a user
2. Get all users who liked a post
3. Check if user liked a specific post

**Design:**
```
Like Item (User's perspective):
  PK: USER#<userId>
  SK: LIKE#POST#<postId>
  EntityType: Like
  GSI1PK: POST#<postId>
  GSI1SK: LIKE#USER#<userId>
  PostId: <postId>
  UserId: <userId>
  CreatedAt: 2024-01-01T00:00:00Z
```

**Queries:**
```typescript
// Get all posts liked by user
const likes = await docClient.query({
  TableName: TABLE_NAME,
  KeyConditionExpression: 'PK = :userId AND begins_with(SK, :prefix)',
  ExpressionAttributeValues: {
    ':userId': `USER#${userId}`,
    ':prefix': 'LIKE#POST#',
  },
});

// Get all users who liked a post (using GSI1)
const likes = await docClient.query({
  TableName: TABLE_NAME,
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :postId AND begins_with(GSI1SK, :prefix)',
  ExpressionAttributeValues: {
    ':postId': `POST#${postId}`,
    ':prefix': 'LIKE#USER#',
  },
});

// Check if user liked specific post
const like = await docClient.get({
  TableName: TABLE_NAME,
  Key: {
    PK: `USER#${userId}`,
    SK: `LIKE#POST#${postId}`,
  },
});
```

### Hierarchical Data

**Example: User > Organization > Team > Member**

**Design:**
```
Organization:
  PK: ORG#<orgId>
  SK: METADATA
  EntityType: Organization
  Name: Acme Corp

Team:
  PK: ORG#<orgId>
  SK: TEAM#<teamId>
  EntityType: Team
  GSI1PK: TEAM#<teamId>
  GSI1SK: TEAM#<teamId>
  TeamId: <teamId>
  Name: Engineering

Member:
  PK: ORG#<orgId>
  SK: MEMBER#<userId>
  EntityType: Member
  GSI1PK: USER#<userId>
  GSI1SK: MEMBER#ORG#<orgId>
  UserId: <userId>
  Role: Admin
```

**Queries:**
```typescript
// Get organization with all teams
const result = await docClient.query({
  TableName: TABLE_NAME,
  KeyConditionExpression: 'PK = :orgId',
  ExpressionAttributeValues: {
    ':orgId': `ORG#${orgId}`,
  },
});

// Get all organizations a user is member of (using GSI1)
const memberships = await docClient.query({
  TableName: TABLE_NAME,
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :userId AND begins_with(GSI1SK, :prefix)',
  ExpressionAttributeValues: {
    ':userId': `USER#${userId}`,
    ':prefix': 'MEMBER#ORG#',
  },
});
```

## Global Secondary Indexes (GSIs)

### When to Use GSIs
- Query by different attributes than PK/SK
- Support alternative access patterns
- Enable reverse lookups (e.g., find user by email)

### GSI Best Practices
1. **Limit to 2-3 GSIs** - More GSIs = more cost and complexity
2. **Project only needed attributes** - Use `KEYS_ONLY` or `INCLUDE` projections
3. **Consider cardinality** - High cardinality in partition keys prevents hot partitions
4. **Overload GSI keys** - Use generic names (GSI1PK, GSI1SK) for flexibility

### GSI Configuration

**GSI1 (Common reverse lookups)**:
```
GSI1PK → GSI1SK
```

**GSI2 (Global queries)**:
```
GSI2PK → GSI2SK
```

**Example GSI Setup:**
```typescript
const tableDefinition = {
  TableName: TABLE_NAME,
  KeySchema: [
    { AttributeName: 'PK', KeyType: 'HASH' },
    { AttributeName: 'SK', KeyType: 'RANGE' },
  ],
  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' },
  ],
  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' },
    },
  ],
  BillingMode: 'PAY_PER_REQUEST',
};
```

## Operations

### Create Item
```typescript
import { DynamoDBDocumentClient, PutCommand } from '@aws-sdk/lib-dynamodb';

await docClient.send(new PutCommand({
  TableName: TABLE_NAME,
  Item: {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
    EntityType: 'User',
    Email: email,
    Name: name,
    CreatedAt: new Date().toISOString(),
    UpdatedAt: new Date().toISOString(),
  },
}));
```

### Read Item
```typescript
import { GetCommand } from '@aws-sdk/lib-dynamodb';

const { Item } = await docClient.send(new GetCommand({
  TableName: TABLE_NAME,
  Key: {