Single-table design is a powerful approach to modeling data in DynamoDB because it allows you to store all your data in one place. This approach can simplify your data model and reduce the number of tables you need to manage.

In the traditional approach, you create a separate table for each entity in your application. For example, you might have a table for characters and a table for quests. Each table would have its own primary key and attributes. However, in DynamoDB, this can lead to inefficient queries and complex data models with high read and write costs.

In this take, we will build upon two existing tables for quests and characters and combine them into a single table. This will show you how to model complex relationships between entities in a single table with many different access patterns.

Ready? Let’s get started!

Getting Started

The source code for this article is in the GitHub repository. We will use TypeScript to write the code and the AWS SDK to interact with DynamoDB.

To spin up a quick TypeScript project, you can use the following commands:

mkdir node-dynamodb-single-table-design
cd node-dynamodb-single-table-design
npm init -y
npm i typescript @aws-sdk/client-dynamodb @aws-sdk/util-dynamodb ulid
npx tsc --init

Data Model

Let’s start by defining our data model with separate tables. This is a good starting point to understand the entities and their relationships. We will have two tables: quests and characters. To make this concrete, let’s define the attributes for each table using TypeScript interfaces.

// models/character.ts
export interface Character {
  username: string // partition key
  class: string
  guild: string
  inventory: Record<string, number>
  total_play_time: number
}
 
// models/quest.ts
export interface Quest {
  username: string // partition key
  quest_id: string // sort key
  quest_name: string
  quest_started_at: string
  quest_completed_at?: string
  checkpoints: number[]
  gold: number

The partition keys are the same for both tables, which is the username. The quest_id is the sort key for the quests table.

Note the character has many quests, and each character has many items in their inventory. These relationships are focused on the following access patterns:

• Get all quests for a character
• Get character by username
• Fetch inventory for a character

The key takeaway here is to understand the relationships between entities and access patterns because they will drive design decisions. In DynamoDB, these relationships and access patterns can be exploited to model the data in a single table.

Combining Entities

For single-table design, we can begin by combining the keys into a generic compound primary key. This will allow us to store both entities in the same table.

// models/game.ts
export interface Game {
  partition_key: string;
  sort_key: string;
  type: "Character" | "Quest";
}

Think of this interface as metadata that will help us differentiate between the entities while giving us the flexibility to store them in the same table. The partition_key will have a prefix, like USER#username, and the sort_key will have a prefix, like CHARACTER#username or QUEST#quest_id. Since both initial tables have the same partition key, the generic partition key will be the same for both entities.

The type attribute will help us differentiate between the entities, and TypeScript will help enforce type safety. This gives us both flexibility and powerful type checking.

With the generic compound primary key, we can now declare what the character item and quest item will look like in the single table.

// models/game.ts
export interface CharacterItem extends Game {
  class: string;
  guild: string;
  inventory: Record<string, number>;
  total_play_time: number;
}
 
export interface QuestItem extends Game {
  quest_name: string;
  quest_started_at: string;
  quest_completed_at?: string;
  checkpoints: number[];
  gold: number;
}

With these interfaces in place, we can express a character in the single table in this way:

{
  partition_key: 'USER#beautifulcoder',
  sort_key: 'CHARACTER#beautifulcoder',
  type: 'Character',
  class: 'Warrior',
  guild: 'Knights of the Round Table',
  inventory: { 'sword': 1, 'shield': 1 },
  total_play_time: 1000
}

The quest item will look like this:

{
  partition_key: 'USER#username',
  sort_key: 'QUEST#01HT7ZKCWJBSEWQRQJPPRFN8MM',
  type: 'Quest',
  quest_name: 'Defeat the Dragon',
  quest_started_at: '2022-01-01T00:00:00Z',
  quest_completed_at: '2022-01-01T01:00:00Z',
  checkpoints: [1, 2, 3],
  gold: 100
}

Each item in the table is optimized specifically for the access patterns we want to support. For example, we can fetch all quests for a character by querying the table with the partition key USER#username. The sort key gives us the ability to filter by the prefix QUEST# to fine-tune the query.

Note: With single-table design, we put heterogeneous data in the same table and use a generic compound primary key to differentiate between entities. This helps reduce the number of round trips to the database and simplifies the data model.

Access Pattern with Frequent Updates

In DynamoDB you get charged for read and write capacity units per table. You can break fast-moving attributes like total_play_time into a separate entity to reduce the cost of updating the entire item.

The character’s inventory likely will not change as frequently as the total_play_time. This allows us to introduce vertical partitioning.

Instead of having a single CharacterItem with all the attributes, we can break it into two entities: CharacterItem and InventoryItem.

// models/game.ts
export interface CharacterItem extends Game {
  class: string;
  guild: string;
  total_play_time: number;
}
 
export interface InventoryItem extends Game {
  inventory: Record<string, number>;
}

Now a single character item will look like this:

{
  partition_key: 'USER#beautifulcoder',
  sort_key: 'CHARACTER',
  type: 'Character',
  class: 'Warrior',
  guild: 'Knights of the Round Table',
  total_play_time: 1000
}

And the inventory item will look like this:

{
  partition_key: 'USER#beautifulcoder',
  sort_key: 'INVENTORY',
  type: 'Inventory',
  inventory: { 'sword': 1, 'shield': 1 }
}

Be sure to go back to the parent interface Game and update the type attribute to include Inventory.

// models/game.ts
export interface Game {
  partition_key: string;
  sort_key: string;
  type: "Character" | "Quest" | "Inventory";
}

This design allows us to update the total_play_time attribute without affecting the inventory.

Create the Single Table

With the data model in place, we can now create the single table in DynamoDB. We will use the AWS CLI to create the table with the following command:

aws dynamodb create-table \
  --table-name game \
  --attribute-definitions AttributeName=partition_key,AttributeType=S AttributeName=sort_key,AttributeType=S \
  --key-schema AttributeName=partition_key,KeyType=HASH AttributeName=sort_key,KeyType=RANGE \
  --provisioned-throughput ReadCapacityUnits=1,WriteCapacityUnits=1

Then we can add items to the table using the AWS SDK in TypeScript.

// add-game-items.ts
import { DynamoDBClient, PutItemCommand } from "@aws-sdk/client-dynamodb";
import { marshall } from "@aws-sdk/util-dynamodb";
import { ulid } from "ulid";
 
import type { CharacterItem, InventoryItem, QuestItem } from "./models/game";
 
const client = new DynamoDBClient();
 
const addCharacter = async (character: CharacterItem): Promise<void> => {
  await client.send(
    new PutItemCommand({
      TableName: "game",
      Item: marshall(character),
    })
  );
};
 
const addInventory = async (inventory: InventoryItem): Promise<void> => {
  await client.send(
    new PutItemCommand({
      TableName: "game",
      Item: marshall(inventory),
    })
  );
};
 
const addQuest = async (quest: QuestItem): Promise<void> => {
  await client.send(
    new PutItemCommand({
      TableName: "game",
      Item: marshall(quest),
    })
  );
};
 
void addCharacter({
  partition_key: "USER#beautifulcoder",
  sort_key: "CHARACTER",
  type: "Character",
  class: "Mage",
  guild: "Hacker",
  total_play_time: 3600,
});
 
void addInventory({
  partition_key: "USER#beautifulcoder",
  sort_key: "INVENTORY",
  type: "Inventory",
  inventory: {
    "Mechanical Keyboard": 1,
    "Kaihl Switches": 100,
    Coffee: 100000,
  },
});
 
const now = new Date();
const twoWeeksFromNow = new Date(Date.now() + 12096e5);
const minuteFromNow = new Date(Date.now() + 60000);
 
void addQuest({
  partition_key: "USER#beautifulcoder",
  sort_key: `QUEST#${ulid(now.getTime())}`,
  type: "Quest",
  quest_name: "Sole Survivor",
  quest_started_at: now.toISOString(),
  quest_completed_at: twoWeeksFromNow.toISOString(),
  checkpoints: [1, 2, 3, 4, 5],
  gold: 1000,
});
 
void addQuest({
  partition_key: "USER#beautifulcoder",
  sort_key: `QUEST#${ulid(minuteFromNow.getTime())}`,
  type: "Quest",
  quest_name: "A Lost Cause",
  quest_started_at: now.toISOString(),
  checkpoints: [6, 7, 8, 9, 10],
  gold: 10000,
});

You should see the items added to the table in the AWS Management Console. We recommend having a look at the items in the table to understand how the data is stored in DynamoDB.

Why Single Table Design?

In traditional relational databases, we normalize data to reduce redundancy and improve data integrity. The emphasis is on minimizing pressure on resources like CPU and memory to optimize database performance.

In DynamoDB, the emphasis is on reducing the number of reads and writes to our database to optimize the cost. This is where single-table design shines.

When it comes to fast-moving attributes like total_play_time, do the math. Say, for example, we update the total play time every 30 seconds. If a character item with its inventory takes up 4KB of space, and there are four target indexes, we would incur a cost of 1,920 write capacity units per hour (4KB x 4 x 120). If we break the inventory into a separate entity, we would only incur a cost of 480 write capacity units per hour (1KB x 4 x 120). This is a 75% reduction.

By breaking up entities into smaller pieces via vertical slicing within a single table, we can reduce the cost of updating the entire item when only a few attributes need to be updated. Because DynamoDB charges per table and per read/write capacity unit, this can lead to significant cost savings.

One Table, Many Access Patterns

Each DynamoDB table comes with a cost. You need to configure alarms, monitor metrics, and handle backups and restores. DynamoDB charges based on the provisioned capacity, the amount of data stored, and the number of read and write requests.

DynamoDB does not support joins or complex queries across multiple tables. Each table has its own partition key, and you can only query the partition key and sort key. Joins must be done in the application layer. This means more round trips to the database and more read and write capacity units are consumed when there are multiple tables.

Wrapping Up

In this post, we've seen how by using a single table design, you can reduce the number of tables you need to manage. This can lead to significant cost savings. You can also reduce the number of round trips to the database and simplify the data model.

Happy coding!

P.S. If you liked this post, subscribe to our JavaScript Sorcery list for a monthly deep dive into more magical JavaScript tips and tricks.

P.P.S. If you need an APM for your Node.js app, go and check out the AppSignal APM for Node.js.