Designing Dynamodb Tables

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:

// 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:

// 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:

// 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:

// 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:

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

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

import { GetCommand } from '@aws-sdk/lib-dynamodb';

const { Item } = await docClient.send(new GetCommand({
  TableName: TABLE_NAME,
  Key: {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
  },
}));

Update Item

import { UpdateCommand } from '@aws-sdk/lib-dynamodb';

await docClient.send(new UpdateCommand({
  TableName: TABLE_NAME,
  Key: {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
  },
  UpdateExpression: 'SET #name = :name, UpdatedAt = :updatedAt',
  ExpressionAttributeNames: {
    '#name': 'Name',
  },
  ExpressionAttributeValues: {
    ':name': newName,
    ':updatedAt': new Date().toISOString(),
  },
}));

Delete Item

import { DeleteCommand } from '@aws-sdk/lib-dynamodb';

await docClient.send(new DeleteCommand({
  TableName: TABLE_NAME,
  Key: {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
  },
}));

Query Items

import { QueryCommand } from '@aws-sdk/lib-dynamodb';

const { Items } = await docClient.send(new QueryCommand({
  TableName: TABLE_NAME,
  KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
  ExpressionAttributeValues: {
    ':pk': `USER#${userId}`,
    ':sk': 'POST#',
  },
  ScanIndexForward: false,
  Limit: 20,
}));

Batch Get

import { BatchGetCommand } from '@aws-sdk/lib-dynamodb';

const { Responses } = await docClient.send(new BatchGetCommand({
  RequestItems: {
    [TABLE_NAME]: {
      Keys: [
        { PK: `USER#${userId1}`, SK: 'PROFILE' },
        { PK: `USER#${userId2}`, SK: 'PROFILE' },
        { PK: `USER#${userId3}`, SK: 'PROFILE' },
      ],
    },
  },
}));

Batch Write

import { BatchWriteCommand } from '@aws-sdk/lib-dynamodb';

await docClient.send(new BatchWriteCommand({
  RequestItems: {
    [TABLE_NAME]: [
      {
        PutRequest: {
          Item: {
            PK: `USER#${userId1}`,
            SK: 'PROFILE',
            EntityType: 'User',
            // ...
          },
        },
      },
      {
        PutRequest: {
          Item: {
            PK: `USER#${userId2}`,
            SK: 'PROFILE',
            EntityType: 'User',
            // ...
          },
        },
      },
    ],
  },
}));

Transactions

import { TransactWriteCommand } from '@aws-sdk/lib-dynamodb';

await docClient.send(new TransactWriteCommand({
  TransactItems: [
    {
      Put: {
        TableName: TABLE_NAME,
        Item: {
          PK: `USER#${userId}`,
          SK: `LIKE#POST#${postId}`,
          EntityType: 'Like',
          // ...
        },
      },
    },
    {
      Update: {
        TableName: TABLE_NAME,
        Key: {
          PK: `USER#${postAuthorId}`,
          SK: `POST#${timestamp}#${postId}`,
        },
        UpdateExpression: 'SET LikeCount = LikeCount + :inc',
        ExpressionAttributeValues: {
          ':inc': 1,
        },
      },
    },
  ],
}));

TypeScript Types

Define Entity Types

// lib/db/types.ts
export interface BaseEntity {
  PK: string;
  SK: string;
  EntityType: string;
  CreatedAt: string;
  UpdatedAt: string;
}

export interface User extends BaseEntity {
  EntityType: 'User';
  Email: string;
  Name: string;
  GSI1PK: string; // USER#<email>
  GSI1SK: string; // USER#<email>
}

export interface Post extends BaseEntity {
  EntityType: 'Post';
  PostId: string;
  Title: string;
  Content: string;
  GSI1PK: string; // POST#<postId>
  GSI1SK: string; // POST#<postId>
  GSI2PK: string; // ALL_POSTS
  GSI2SK: string; // POST#<timestamp>
}

export type Entity = User | Post;

Helper Functions

// lib/db/helpers.ts
export function createUserKey(userId: string) {
  return {
    PK: `USER#${userId}`,
    SK: 'PROFILE',
  };
}

export function createPostKey(userId: string, timestamp: number, postId: string) {
  return {
    PK: `USER#${userId}`,
    SK: `POST#${timestamp}#${postId}`,
  };
}

export function parsePostId(sk: string): string {
  const [, , postId] = sk.split('#');
  return postId;
}

Design Process

When designing a new table:

1. List all access patterns first

   • How will data be queried?
   • What filters/sorts are needed?
   • What relationships exist?

2. Design primary key to satisfy most common pattern

   • Usually: entity lookups by ID
   • Use composite SK for sorting (e.g., POST#<timestamp>)

3. Add GSI1 for reverse lookups

   • Find user by email
   • Find post by ID
   • Find entity by unique attribute

4. Add GSI2 for global queries (if needed)

   • Get all posts (across users)
   • Get all public content
   • Time-series queries

5. Test query patterns

   • Write example queries for each access pattern
   • Ensure no table scans
   • Verify performance characteristics

Anti-Patterns to Avoid

1. Multiple tables - Always single-table
2. Table scans - Always query with PK (and optionally SK)
3. Too many GSIs - Limit to 2-3
4. Normalized design - Denormalize, duplicate data when needed
5. String concatenation in queries - Use begins_with() for prefixes
6. Large items - Keep items under 400KB (ideally much smaller)
7. Hot partitions - Ensure high cardinality in partition keys

Performance Optimization

1. Co-locate related data - Same partition key for items queried together
2. Use sort keys effectively - Enable range queries and sorting
3. Project only needed attributes - Use sparse GSIs with INCLUDE projection
4. Batch operations - Use BatchGet/BatchWrite for multiple items
5. Conditional writes - Prevent race conditions with condition expressions
6. TTL for ephemeral data - Auto-delete expired items
7. DynamoDB Streams - Track changes, build derived data

You design single-table schemas that are fast, cost-effective, and scale infinitely. Period.