Real-World Implementation of Single Table Design in AWS DynamoDB

Introduction

As web applications scale and diversify, traditional relational database models often become bottlenecks for performance and agility. Enter DynamoDB with Single Table Design (STD)—a strategy that aligns your data model with actual access patterns, delivering ultra-fast, scalable performance. In this blog, we’ll walk through applying STD in a real-world web app that manages Quality managers, Technicians, devices, and inspections.

Use Case: Manufacturing Quality Control System

We’re building a Work Order (WO)-driven system to manage production quality control. The system will support both quality managers and floor technicians with tailored access via web and mobile platforms. It needs to efficiently handle data related to users, WOs, measurement tools, and quality standards – all with low latency and high scalability.

Problem Statement

Design a system to manage interactions between quality managers, technicians, measurement devices, and WO data, supporting fast, role-specific access patterns.

• Quality Managers: Create and manage technicians, Schedule and assign WOs to production lines, Track devices and Generate quality reports from WO results
• Technicians:Execute WOs using calibrated devices,Record product measurements against WO specifications,Upload WO completion data for manager review
• Devices: Require tracking for calibration status and maintenance cycles
• Metadata: Defines WO test parameters (tolerances, test methods),Classifies defect types for WO analysis

What is Single Table Design (STD) in DynamoDB?

Single Table Design (STD) is a strategy where all entities in your application—such as users, devices, work orders, and metadata—are stored in a single DynamoDB table. Instead of creating a separate table for each entity, you design your keys and indexes around how your application accesses the data, not how it’s structured logically.

Key Concepts

• Shared Table: All data types (users, devices, etc.) live in one table.
• Access Pattern–First Design: Keys and indexes are structured based on how data is queried.
• Composite Keys: Every item uses a combination of a partition key (PK) and sort key (SK) to uniquely identify and organize data.
• Entity Prefixing: Prefixes like USER# or DEVICE# are used in keys to avoid collisions and make querying easier.
• Global Secondary Indexes (GSIs): Additional indexes let you query data in multiple ways.

Why AWS Recommends Single Table Design

AWS advocates for STD in DynamoDB to:

• Reduce the number of read/write operations needed to join or aggregate data.
• Improve query performance by minimizing the number of round-trips to the database.
• Simplify data modeling for real-time applications.
• Leverage the high throughput and scalability of DynamoDB efficiently with well-designed keys and indexes.
• Enable atomic operations across related items using DynamoDB transactions.

The Traditional Approach (Multi-Table)

Our Single Table Solution

Designing the Schema Based on Access Patterns

Step 1: Identify the Core Entities

• Users (Quality Manager, Technicians)
• Devices
• Work Orders (WOs)
• Metadata Tolerances, Test Methods, Defects

Step 2: Primary Key Design (PK/SK)

Step 3: Global Secondary Indexes (GSIs)

Step 4: Access Pattern Mapping

Below is a breakdown of how the access patterns map to our schema:

Step 5: Example Data Model

1. User (Manager / Technician):
   • PK: USER#johndoe
   • SK: USER#johndoe
   • Type: Technician
   • Status: Active
   • CertificateExpiry: 2026-12-31
2. Work Order (WO):
   • PK: WO#12345
   • SK: USER#johndoe
   • ProjectID: P123
   • ScheduledDate: 2025-03-10
   • Status: Pending
3. Device:
   • PK: DEVICE#D123
   • SK: USER#johndoe
   • Model: M-001
   • SerialNumber: SN12345678
4. Metadata (Tolerance):
   • PK: METADATA#TOLERANCE
   • SK: METADATA#TOL_025

Step 6: Querying the Data

1. Get Technicians User Profile

Use Case: Technician logs into the app; we want their profile.

• PK: USER#tech001
• SK: USER#tech001
• Query: Query using PK = USER#tech001 AND SK = USER#tech001

{

  “KeyConditionExpression”: “PK = :pk AND SK = :sk”,

  “ExpressionAttributeValues”: {

    “:pk”: “USER#tech001”,

    “:sk”: “USER#tech001”

  }

}

2. List All Work Orders for a Specific Technician

Use Case: Technician sees their assigned WOs.

• GSI3 PK: USER#tech001
• SK begins_with: WO#

{

  “IndexName”: “GSI3”,

  “KeyConditionExpression”: “PK = :pk AND begins_with(SK, :skprefix)”,

  “ExpressionAttributeValues”: {

    “:pk”: “USER#tech001”,

    “:skprefix”: “WO#”

  }

}

3. List Work Orders Under a Project

Use Case: Manager filters WOs by project.

• GSI2 PK: PROJECT#P001
• SK begins_with: WO#

{

  “IndexName”: “GSI2”,

  “KeyConditionExpression”: “PK = :project AND begins_with(SK, :wo)”,

  “ExpressionAttributeValues”: {

    “:project”: “PROJECT#P001”,

    “:wo”: “WO#”

  }

}

Users (Admins & Inspectors)

Devices

Work Orders (WOs)

GSI Entries (e.g., for Project and User Work Order Indexing)

Metadata (Room Types, Substrates, etc.)

1. Query Inspector Users Sorted by License Expiry: Using GSI1, query all users with the “Inspector” type and sort by license expiry.
2. Get All Work Orders for a Project: Using GSI2, list all work orders under a particular project ID and apply filters as necessary.
3. Get Devices for a User: Query by USER#<username> as the partition key and sort by device ID.

Lessons Learned

Pros

• Blazing-fast queries: With access-pattern-based keys and no joins, query latency is incredibly low.
• Simplified scaling: DynamoDB’s architecture handles throughput spikes and high concurrency with ease.
• Cost-effective: You pay for what you use—queries, reads, and writes—without managing multiple tables or servers.

Challenges

• Steep learning curve: Designing a single-table schema requires you to know your access patterns before you write your first line of code.
• Debugging complexity: With all data types in one table, naming conventions and key discipline are critical to avoid chaos.

When to Use (and When Not to Use) Single Table Design

When It Makes Sense

• Well-defined access patterns: Ideal when you know exactly how data will be queried.
• High-performance needs: Perfect for apps that require low-latency responses and scalability.
• Minimizing joins: Works great if your data is related and you want to reduce complex joins.
• Tightly linked entities: Best when entities (e.g., users, devices) share strong relationships.

When It’s Not Ideal

• Evolving data access patterns: Avoid if your app is still figuring out how data will be queried.
• Complex relational queries: Not suitable if you rely on complex joins and foreign keys.
• Independent data types: If entities don’t interact much, separate tables might be simpler.
• Frequent ad-hoc querying: Limited flexibility for exploratory or analytical queries.

Conclusion

Single Table Design in DynamoDB is a powerful strategy that simplifies data modeling while optimizing performance for real-time applications. By unifying all data into a single table and designing keys and GSIs around your application’s access patterns, you can significantly improve query efficiency, reduce operational complexity, and better align with how DynamoDB is built to scale.

AWS recommends this approach because it embraces access-pattern-first design, making your application leaner, faster, and more scalable in the long run.