Single-Table Design in DynamoDB: Building a Medical Test Booking System
In Part 1, we looked at the basics of DynamoDB, including what it is, how it stores data, and how it differs from traditional relational databases. We also covered concepts like partition keys, sort keys, queries, scans, and indexes. Now, we will design a single-table schema that uses these ideas to create a real-world serverless application: a medical test booking system.
The key principle of single-table design is to organize your table around access patterns instead of entities. This requires shifting from relational normalization to denormalization, allowing different entity types to exist in one table. In the cloud, storage is cheap compared to compute and query costs, making this method efficient and cost-effective.
Understanding the Use Case
The medical test booking system involves multiple entities and relationships. Each patient can book several tests. Each test is linked to a test type template, and doctors provide results with descriptions and explanations. In a relational model, you would typically have separate tables for patients, bookings, tests, test types, and test results. In DynamoDB, we combine these entities into a single table and organize items by access patterns.
Before we design the table, we will define the key access patterns:
- Retrieve all bookings for a patient.
- Retrieve all tests for a specific booking.
- Retrieve a test result for a specific test.
- Retrieve all tests of a particular test type.
- Retrieve all unreviewed tests for doctor review.
- Retrieve reference data for a test type.
Designing the table around these queries ensures that each operation is efficient by using queries or indexes instead of costly scans.
Single Table Design Strategy
The essence of single-table design is choosing the right partition key (PK) and sort key (SK) for each item type. We will also use GSIs to support alternative access patterns.
Here’s how we can structure our MedicalTests table:
Item Types
We will store the following item types in the same table:
- PATIENT – basic patient profile.
- BOOKING – a booking made by a patient.
- TEST – each test included in a booking.
- TESTRESULT – doctor’s results for a test.
- TESTTYPE – reference data defining each type of test.
Primary Key Strategy
We will use a composite PK/SK design:
- The partition key identifies a grouping, often the patient or entity type.
- The sort key orders related items within that partition, like bookings or tests within a patient.
Example Key Patterns
Patients
PK = PATIENT#<PatientId>
SK = PROFILE
This item stores a patient’s basic information:
| PK | SK | Attributes |
|---|
| PATIENT#123 | PROFILE | Name: John Doe, DOB: 1990-01-01 |
Bookings for a Patient
PK = PATIENT#<PatientId>
SK = BOOKING#<BookingId>
Each booking is a separate item:
| PK | SK | Attributes |
|---|
| PATIENT#123 | BOOKING#001 | BookingDate: 2025-12-01 |
| PATIENT#123 | BOOKING#002 | BookingDate: 2025-12-15 |
Tests within a Booking
PK = PATIENT#<PatientId>
SK = BOOKING#<BookingId>#TEST#<TestId>
Each test is an item under the patient partition:
| PK | SK | Attributes |
|---|
| PATIENT#123 | BOOKING#001#TEST#A1 | TestType: BloodTest, Status: Pending |
| PATIENT#123 | BOOKING#001#TEST#A2 | TestType: XRay, Status: Pending |
Test Results
PK = PATIENT#<PatientId>
SK = BOOKING#<BookingId>#TEST#<TestId>#RESULT
Doctors record results here:
| PK | SK | Attributes |
|---|
| PATIENT#123 | BOOKING#001#TEST#A1#RESULT | Result: Normal, Notes: Healthy blood levels |
Test Type Reference Data
PK = TESTTYPE#<TypeId>
SK = METADATA
Stores template info for each test:
| PK | SK | Attributes |
|---|
| TESTTYPE#BloodTest | METADATA | Name: Blood Test, Description: Measures blood components |
Supporting Alternative Access Patterns with Global Secondary Indexes
To support queries beyond the primary key, we define GSIs.
GSI1: Retrieve all tests in a booking
GSI1PK = BOOKING#<BookingId>
GSI1SK = TEST#<TestId>
GSI2: Retrieve all tests of a specific test type
GSI2PK = TESTTYPE#<TypeId>
GSI2SK = TEST#<TestId>
GSI3: Retrieve unreviewed tests
GSI3PK = TESTSTATUS#PENDING_REVIEW
GSI3SK = PATIENT#<PatientId>#BOOKING#<BookingId>#TEST#<TestId>
These indexes allow efficient queries that would otherwise require scans.
Query Examples
Retrieving data is simple. To get all bookings for a patient, query using PK = PATIENT#123 and SK beginning with BOOKING#. To get all tests in a booking, query GSI1 using BOOKING#001 as the partition key. Doctors can easily find all pending tests using GSI3, bypassing costly scans across the table.
Why Single Table Design Works
Single-table design in DynamoDB reduces operational complexity, ensures predictable performance, and lowers read and write costs by combining related items into the same partition. By organizing your table around access patterns, you eliminate the need for joins, avoid expensive scan operations, and keep queries fast and efficient as data grows.
The Medical Test Booking System shows how different entity types—patients, bookings, tests, results, and test type templates—can coexist in one table while supporting all the needed queries through careful PK/SK design and well-planned GSIs.
Single-table design may seem strange at first, especially for those familiar with relational databases. However, once you adopt access pattern-driven design, DynamoDB becomes a strong, scalable foundation for serverless applications. Developers gain both speed and flexibility while ensuring operational and cost efficiency stays in focus.
