DynamoDB Single-Table Design: Access Patterns First

Most DynamoDB projects fail not because the database is slow, but because the data model was copied from a relational ERD. You wouldn’t use a hammer to drive a screw, and you shouldn’t use normalized tables to serve a key-value store’s access patterns. DynamoDB has no joins, no foreign keys, and no server-side aggregation—so if you model for “entities” instead of “queries,” you’ll end up with scatter-gather code, N+1 request loops, and throttling at scale. The fix is to invert your thinking: start with the questions your application asks, then design a single table that answers them in one round trip.

Why Traditional Data Modeling Breaks in DynamoDB

Relational modeling optimizes for data integrity and storage normalization. You split a customer into customers, orders, and order_items, then join them at read time. DynamoDB charges per read request unit, and every join you simulate with multiple GetItem or Query calls multiplies latency and cost. Worse, DynamoDB’s 1 MB limit per operation means a “join” across three tables could require several round trips, each with its own consistency and error handling.

Traditional modeling also assumes you can add indexes after the fact. In DynamoDB, secondary indexes are eventually consistent copies—you must define them upfront, and you pay for every write to them. If you discover a new access pattern later, you can’t just add a column; you must backfill a new index or, worse, migrate to a new table. The relational schema is a liability, not an asset, in this environment.

Access Patterns: The Only Schema That Matters

Before you write a single CreateTable call, list every query your application will execute. For each, specify: the entity type, the lookup key (exact match or range), the sort order, and the fields you need to return. Do not think about “customer” or “order” as tables—think of them as item collections that share a partition key.

For example, a typical e-commerce app might need:

• Fetch a user by email (exact match)
• Fetch all orders for a user, sorted by date descending
• Fetch a single order by order ID
• Fetch all items in an order
• Fetch all users in a given status (e.g., “active”)

Each of those is an access pattern. If you can’t answer a pattern with one Query or GetItem, your schema is wrong. Write these patterns on a whiteboard before you touch the console—they are your schema.

Rule of thumb: If you need more than one round trip to serve a single screen, you’re modeling for the database, not for the user.

From Patterns to Primary Keys: A Worked Example

Let’s design for the patterns above. We’ll use a single table called app_data with a composite primary key: PK (partition key) and SK (sort key). The trick is to overload these keys so that different entity types coexist in the same table.

For user lookup by email, set PK = USER#<email> and SK = PROFILE. For orders by user, set PK = USER#<email> and SK = ORDER#<order_id>. For items in an order, set PK = ORDER#<order_id> and SK = ITEM#<sku>. Now a single Query on PK = USER#alice@example.com with SK begins_with ORDER# returns all orders, sorted by SK—but you need date sorting, not ID sorting. So store the timestamp in the sort key: SK = ORDER#<unix_timestamp>#<order_id>.

Here’s the item structure:

{
  "PK": "USER#alice@example.com",
  "SK": "ORDER#1750000000#ORD-12345",
  "type": "order",
  "order_total": 129.99,
  "status": "shipped",
  "ship_address": "123 Main St"
}

To fetch a single order, you don’t know the timestamp—so add a global secondary index (GSI) with GSI1PK = ORDER#<order_id> and GSI1SK = USER#<email>. That gives you exact-match lookup by order ID. The table now serves four patterns with two key structures and one GSI.

Handling Complex Queries with Sparse Indexes and Overloading

Not every pattern fits a simple prefix on the sort key. For “all active users,” you need a filter—but filters in DynamoDB scan everything that matches the key, which is inefficient. Instead, create a sparse GSI. A sparse index only contains items that have a specific attribute. Set GSI2PK = STATUS#<status> and GSI2SK = USER#<email>, and only include that attribute on user profile items where status = “active”. Then Query on GSI2PK = STATUS#active returns only active users—no filter, no scan.

Key overloading means the same attribute name holds different meanings depending on the item type. For example, GSI1PK might hold ORDER#<id> for order items, but PRODUCT#<sku> for product items. This lets you reuse a single index for multiple entity types, but it requires rigorous naming conventions. Always prefix your key values with a type name—never use raw IDs—to avoid collisions.

Avoiding Hot Partitions Through Key Design

If all your traffic hits one partition key, you’ll throttle even if your total throughput is fine. For example, PK = USER#alice@example.com is fine for a single user, but if you put all orders under PK = STATUS#shipped, that one partition becomes a bottleneck. Spread writes across many partition keys.

For high-volume entities like orders, use a composite partition key that includes a time-based element, but be careful—if you use ORDER#2025-01-01, you’ll still get a hot partition for today’s date. Instead, use a random suffix or a shard count. For example, PK = ORDER#<hash_of_id>#<shard> where shard is a number from 0 to N. Then query by fanning out across shards, or use a GSI for the actual lookup. A simpler pattern for most apps: keep the user as the partition key, because user-level access is naturally distributed across many users.

// Bad: all orders for a day in one partition
PK = "ORDER#2025-03-01"

// Better: distribute by order ID hash
PK = "ORDER#" + Math.abs(orderId.hashCode() % 100)

When Single-Table Design Isn’t the Answer

Single-table design is not a dogma. If you have entities with completely disjoint access patterns, no shared keys, and no need to query across them, separate tables can be simpler. For example, a “user sessions” table with TTL and a “product catalog” table with frequent updates have nothing in common—forcing them together adds complexity without benefit.

Also avoid single-table if your team is new to DynamoDB and you’re building a quick prototype. The learning curve for key overloading and sparse indexes is steep. And if you need transactional integrity across multiple entity types, DynamoDB transactions work across tables, so you don’t gain anything by co-locating. Finally, if you’re using an ORM or a framework that assumes one table per class, you’ll fight the tooling constantly.

Migration Strategy: Refactoring an Existing Multi-Table Model

You can’t just “alter” a DynamoDB table. To migrate from a multi-table model, create a new table with the desired composite key structure. Then write a one-time export job that reads from the old tables and writes to the new one, transforming keys and adding the necessary type prefixes. Use DynamoDB’s scan or export to S3, then import with a script that maps old attributes to new PK/SK values.

During migration, run both old and new tables in parallel. Update your application code to read from the new table first, falling back to the old if the item isn’t found. Once you’ve verified data integrity and performance, flip the write path. Finally, delete the old tables. Do not attempt an in-place migration—DynamoDB has no rename or rebuild-in-place operation.

The short version

Single-table design in DynamoDB is not about saving storage—it’s about saving round trips. Start by listing every access pattern, then design a composite primary key where the partition key identifies the main entity and the sort key encodes the relationship and sort order. Use GSIs sparingly, but use sparse indexes for filtered queries. Distribute your partition keys to avoid hot spots. And remember: if a pattern doesn’t fit, it’s okay to use a second table—just make that a conscious decision, not a default habit.