DynamoDB Single Table Design: The Senior Engineer's Guide

Single Table Design is often considered the "Holy Grail" of DynamoDB modeling. Instead of creating a table for every entity, you store multiple entity types in a single table, using generic primary keys like PK and SK.

Why Single Table Design?

1. Performance: You can fetch an entity and all its related items (e.g., an Order and its OrderItems) in a single Query call.
2. Cost: Reduces the overhead of managing multiple tables and their individual provisioned throughput.
3. Simplicity: One table to monitor, backup, and scale.

The Core Concept: Generic PK/SK

Instead of user_id or order_id, we use:

• PK (Partition Key): e.g., USER#123 or ORDER#ABC
• SK (Sort Key): e.g., METADATA or ITEM#456

Modeling Relationships

1. One-to-Many (1:N)

To store a User and their many Addresses:

• User Item: PK=USER#123, SK=METADATA
• Address Item: PK=USER#123, SK=ADDR#789

By calling Query(PK='USER#123'), you get the user profile and all their addresses in one shot.

2. Many-to-Many (M:N)

To store Students and their Courses, we use an Adjacency List pattern:

• Student-Course Link: PK=STUDENT#1, SK=COURSE#MATH
• Course-Student Link (via GSI): We create a GSI where the SK is the Partition Key. This allows us to query "All courses for a student" and "All students for a course."

GSI Overloading

GSI Overloading is the secret sauce. You can use the same GSI columns (GSI1_PK, GSI1_SK) to store different attributes depending on the entity type. This prevents you from hitting the 20 GSI limit per table.

When NOT to use Single Table Design

• Analytics: If you need to perform ad-hoc scans across the entire dataset.
• Varying Access Patterns: If your access patterns change frequently, a rigid single table can become a maintenance nightmare.
• Exporting to Data Lake: Multi-table designs are often easier to process in Glue/Athena.

Summary

Single Table Design is about shifting the complexity from the database engine to your application logic. By pre-joining your data at write time, you achieve the massive, consistent scale that DynamoDB is famous for.