Mastering Single Table Design in DynamoDB: A Complete Guide

1. What is Single Table Design?

Traditionally, in relational databases, you’d store each entity type in its own table:

• Users table

• Orders table

• Products table

Relationships are modeled with foreign keys and joined at query time.

DynamoDB works differently:

• No joins

• Limited query operators

• Query speed depends on partition key access

Single Table Design means all entity types live in the same table, differentiated by composite primary keys and type attributes.

Example table snippet:

Here:

• PK: Groups related items (User ID, Product ID, Category ID, etc.)

• SK: Describes the specific item type or a timestamped version

2. Why Single Table Design Matters

DynamoDB pricing is based on read/write units. Scans (reading the whole table) are expensive and slow. If you store everything in separate tables and need to fetch related data, you’ll either:

• Make multiple network calls

• Perform expensive scans

Single Table Design:

• Reduces queries to O(1) lookups

• Eliminates joins and multi-table fetches

• Centralizes throughput and indexes

• Enables atomic writes across different item types in the same partition

3. Core Design Principles

Before touching DynamoDB, follow these rules:

3.1 Start with Access Patterns

You do not start with entities. You start with the questions your application will ask.

Example:

1. Get a user profile by ID

2. Get all orders for a given user

3. Get all products in a category

4. Search orders by date

5. Fetch product details by ID

Every query must be answered by a Query operation using:

• Partition Key + optional Sort Key condition

• Or a Global Secondary Index (GSI)

3.2 Composite Keys

DynamoDB’s primary key = Partition Key (PK) + Sort Key (SK).

PK decides where data lives. All items with the same PK are stored together.
SK decides order within the partition and allows range queries.

Example:

• PK = USER#123

• SK = ORDER#2025-08-01

Query: "All orders for user 123" →
PK = USER#123 and SK begins_with ORDER#

3.3 Entity Type Prefixing

Always prefix PKs and SKs with entity type:

• USER#123

• ORDER#001

• PRODUCT#987

Benefits:

• Avoids collisions

• Makes data human-readable

• Simplifies debugging

3.4 Denormalization

In STD, duplication is intentional.
If you need to fetch an order and its product details together, store product details in the order item. Storage is cheap; extra queries are not.

4. Example: E-Commerce in a Single Table

4.1 Requirements

We want to:

1. Fetch user profile by ID

2. Fetch all orders for a user

3. Fetch product details by ID

4. Fetch all products in a category

5. Fetch all orders on a specific date

4.2 Table Schema

Primary Key:

• PK: Entity grouping (User ID, Product ID, Category ID)

• SK: Entity type or context

4.3 Queries

• User profile: PK = USER#123 and SK = PROFILE

• User orders: PK = USER#123 and SK begins_with ORDER#

• Product details: PK = PRODUCT#987 and SK = METADATA

• Category products: PK = CATEGORY#Shoes and SK begins_with PRODUCT#

4.4 Global Secondary Index

To get all orders by date across users:

• GSI1PK = ORDER

• GSI1SK = orderDate

Order item example:

Query GSI1:
GSI1PK = ORDER and GSI1SK = 2025-08-01

5. Advanced Techniques

5.1 Sparse Indexes

If only certain items have a GSI attribute, that index is "sparse" and contains only relevant items. Useful for filtering by status, active flags, etc.

5.2 Time-Ordered SKs

Storing timestamps in SK (ORDER#<ISODate>) allows:

• Range queries (BETWEEN)

• Latest-first reads (when inverted with negative timestamp)

5.3 Aggregates & Summary Items

Instead of calculating aggregates on the fly:

• Store summary items in the same partition

• Example: PK = USER#123, SK = ORDER_SUMMARY, totalOrders = 42

5.4 Adjacency Lists

To model many-to-many relationships, store linking items:

• PK = USER#123, SK = PRODUCT#987

• PK = PRODUCT#987, SK = USER#123

6. Common Mistakes

1. No Access Pattern Planning
   → Leads to costly scans.
   Solution: Write out queries first.

2. Overusing GSIs
   → Increases cost and write latency.
   Solution: Reuse indexes where possible.

3. Too Clever Keys
   → Impossible to debug.
   Solution: Keep keys readable.

4. Relational Mindset
   → Trying to normalize data.
   Solution: Duplicate when it saves queries.

7. Best Practices Checklist

• Start with all queries your app will run

• Use type prefixes for PK/SK

• Group related items in same PK

• Denormalize data for query speed

• Use sparse GSIs for selective queries

• Keep schema documented with examples

Final Thoughts

Single Table Design requires a mindset shift:

• SQL: Store data normalized → join at query time

• DynamoDB: Decide queries → shape data for them

Once you embrace this, DynamoDB becomes blazingly fast and cost-efficient. You’ll spend less time fighting scans and more time delivering features.