Skip to content
WritingArticle
  • #serverless
  • #aws
  • #dynamodb
  • #database
  • #data-modeling

DynamoDB Single-Table Design for Beginners: Model from Access Patterns, Not Tables

Pawan Joshi

6 min read

Share

If you've learned SQL first, DynamoDB feels strange. There are no joins, you can't query any column you like, and the advice to "put everything in one table" sounds wrong.

It stops feeling strange once you see the core idea: in DynamoDB, you design the table around the questions your app asks. Get that right and DynamoDB answers those questions in single-digit milliseconds at any scale.

The two keys that matter

Every item has a primary key, made of:

  • Partition key (PK): decides which partition stores the item. Items with the same PK live together.
  • Sort key (SK): orders items inside a partition, and lets you query ranges like "starts with" or "between".

The most important operation is Query: "give me items with this PK, optionally filtered by SK." It's fast and cheap because DynamoDB reads exactly the items you asked for.

Scan reads the whole table and filters afterwards. It gets slower and more expensive as the table grows. Good designs almost never need it.

Step 1: List your access patterns first

Before you create anything, write down every question the app asks. For a student community app:

#Access pattern
1Get a member's profile
2List events, newest first
3Get one event with its details
4List everyone registered for an event
5List every event a member registered for
6Check whether a member is registered for an event

In SQL you'd design tables first and write queries later. In DynamoDB, this list is the design.

Step 2: Choose keys that answer them

We'll use generic key names, PK and SK, so different item types can share one table. A prefix in each key value tells you what the item is.

ItemPKSKOther attributes
Member profileMEMBER#m123PROFILEname, email, college
EventEVENT#e42METAtitle, date, venue
RegistrationEVENT#e42MEMBER#m123registeredAt, memberName

Now look at how the patterns map:

  • Pattern 1, member profile: GetItem with PK MEMBER#m123, SK PROFILE.
  • Pattern 3, one event: GetItem with PK EVENT#e42, SK META.
  • Pattern 4, everyone registered: Query PK EVENT#e42 where SK begins with MEMBER#. One request returns the whole list.
  • Pattern 6, is this member registered: GetItem with PK EVENT#e42, SK MEMBER#m123.

Storing the registration inside the event's partition is what makes pattern 4 a single query. This is the heart of single-table design: put items you read together under the same partition key.

Step 3: Add a GSI for the other direction

Pattern 5 asks the opposite question: all events for one member. The base table is organized by event, so we add a global secondary index (GSI) that flips the keys.

Give each registration two more attributes:

ItemGSI1PKGSI1SK
RegistrationMEMBER#m123EVENT#2026-11-21#e42

Now Query GSI1 with GSI1PK = MEMBER#m123 returns every event that member registered for, sorted by date because the date is at the start of the sort key.

For pattern 2, give each event GSI1PK = EVENTS and GSI1SK = 2026-11-21#e42, then query GSI1 for EVENTS, newest first.

One caution: a constant GSI key like EVENTS puts every event in one partition. That's fine for hundreds or thousands of events. For very high write volume, spread them, for example by year: EVENTS#2026.

The queries in code

import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand, GetCommand } from "@aws-sdk/lib-dynamodb";
 
const db = DynamoDBDocumentClient.from(new DynamoDBClient({}));
const TABLE = process.env.TABLE_NAME;
 
// Pattern 4: everyone registered for an event
export const attendees = (eventId) =>
  db.send(new QueryCommand({
    TableName: TABLE,
    KeyConditionExpression: "PK = :pk AND begins_with(SK, :member)",
    ExpressionAttributeValues: { ":pk": `EVENT#${eventId}`, ":member": "MEMBER#" },
  }));
 
// Pattern 5: every event a member registered for, newest first
export const eventsForMember = (memberId) =>
  db.send(new QueryCommand({
    TableName: TABLE,
    IndexName: "GSI1",
    KeyConditionExpression: "GSI1PK = :pk AND begins_with(GSI1SK, :event)",
    ExpressionAttributeValues: { ":pk": `MEMBER#${memberId}`, ":event": "EVENT#" },
    ScanIndexForward: false,
  }));
 
// Pattern 6: is this member registered?
export const isRegistered = async (eventId, memberId) => {
  const { Item } = await db.send(new GetCommand({
    TableName: TABLE,
    Key: { PK: `EVENT#${eventId}`, SK: `MEMBER#${memberId}` },
  }));
  return Boolean(Item);
};

The table in AWS SAM

CommunityTable:
  Type: AWS::DynamoDB::Table
  Properties:
    BillingMode: PAY_PER_REQUEST
    AttributeDefinitions:
      - { AttributeName: PK, AttributeType: S }
      - { AttributeName: SK, AttributeType: S }
      - { AttributeName: GSI1PK, AttributeType: S }
      - { AttributeName: GSI1SK, AttributeType: S }
    KeySchema:
      - { AttributeName: PK, KeyType: HASH }
      - { AttributeName: SK, KeyType: RANGE }
    GlobalSecondaryIndexes:
      - IndexName: GSI1
        KeySchema:
          - { AttributeName: GSI1PK, KeyType: HASH }
          - { AttributeName: GSI1SK, KeyType: RANGE }
        Projection: { ProjectionType: ALL }

Rules of thumb

  1. Write the access patterns before the table. If a new feature adds a pattern, add an index or reshape the keys deliberately.
  2. Use Query, avoid Scan. If you need a Scan in a request path, the model is missing an access pattern.
  3. Copy data where it saves a read. Storing memberName on the registration means the attendee list needs no second lookup. Duplication is normal in DynamoDB.
  4. Use conditional writes for rules. attribute_not_exists(PK) prevents duplicate registrations without a race condition.
  5. Use transactions sparingly. TransactWriteItems keeps related items consistent, such as a registration and a seat counter, but costs more than single writes.
  6. Start with on-demand capacity. Switch to provisioned capacity only when traffic is steady and predictable.

When not to use a single table

Single-table design is a tool, not a rule. Use separate tables when:

  • Access patterns are unknown or change constantly, as in early prototypes or analytics. A relational database or several simple tables may serve you better.
  • Different data has different needs, such as separate backup, retention, encryption or ownership by different teams.
  • You need ad hoc queries and reports. Export DynamoDB to S3 and query it with Amazon Athena instead of forcing analytics into the request path.
  • Your team is new to DynamoDB. One table per entity with a clear key is a perfectly good start. You can consolidate later.

Key takeaways

  • DynamoDB is designed around access patterns, not entities.
  • Put items you read together under the same partition key, and use the sort key to group and order them.
  • Add GSIs to answer questions from another direction.
  • Prefer Query over Scan, duplicate data when it saves reads, and enforce rules with conditional writes.

Put this into practice by upgrading the API from Build a Serverless REST API with AWS Lambda, API Gateway and DynamoDB to query by member instead of scanning.