Non-Relational Databases: Single Table or Multi Table?

Non-relational databases have changed how developers think about data modeling. Among them, Amazon DynamoDB stands out for its speed, scalability, and serverless simplicity. This post explores the core ideas behind non-relational databases, explains the philosophies and tradeoffs of single-table versus multi-table designs in DynamoDB, and offers clear conventions and examples to guide implementation.

The rise of serverless and distributed applications has pushed traditional relational databases to their limits. Developers building modern web apps, real-time analytics systems, or global services often need database solutions that can scale instantly, handle unpredictable access patterns, and avoid performance bottlenecks. That’s where non-relational databases come in.

Amazon DynamoDB, a fully managed NoSQL database from AWS, is a common choice for developers needing high availability, low latency, and seamless scalability. But DynamoDB introduces a learning curve, especially when it comes to deciding how to model your data. Should you follow the single-table design approach that stores everything in one table? Or is it better to stick with the multi-table model that mirrors traditional database separation?

To answer that, we’ll walk through:

What non-relational databases are
Why DynamoDB is different
Conventions used in DynamoDB single-table design
Real-world examples
Comparison with multi-table models
Practical use cases and guidance

Understanding Non-Relational Databases

What is a Non-Relational Database?

A non-relational or NoSQL database doesn’t follow the rigid schema structure of a relational database. Instead of using rows and tables with foreign keys and joins, non-relational databases often use key-value pairs, documents, graphs, or column families.

DynamoDB is a key-value and document database. Each item (like a row) is uniquely identified by a primary key, and there’s no requirement for every item to have the same attributes (like columns). This flexibility is what allows developers to move quickly and iterate on their data model.

Why Choose Non-Relational?

Key advantages include:

Scalability: Automatic partitioning and sharding
Performance: Predictable millisecond latency
Schema-less: Different items can have different structures
High availability: Built-in fault tolerance

DynamoDB: AWS’s Serverless NoSQL Champion

I’ve enjoyed using DynamoDB, it’s simple, fast and efficient. More than that; DynamoDB provides:

On-demand or provisioned capacity
Global tables for multi-region replication
Streams for change tracking
Built-in support for transactions
Secondary indexes for flexible queries

But the key challenge? Modeling your data to fit your access patterns, because DynamoDB doesn’t support joins or complex queries.

Single-Table Design

In relational databases, normalization and table separation are foundational principles. But in DynamoDB, denormalization is often encouraged. Instead of optimizing for storage, you optimize for your queries.

Single-table design means putting all your entities (users, orders, posts, comments, etc.) in one table, distinguished by partition and sort key patterns.

The goal is to reduce the number of queries and avoid joins by co-locating related data using carefully crafted keys.

Key Concepts

Primary Key (PK + SK): Every item in DynamoDB has a partition key (PK), and optionally, a sort key (SK). Together, they uniquely identify an item.
Entity Type Prefixes: Identify the kind of data stored using strings like USER#, ORDER#, POST#.
Attribute Overloading: Reuse the same attribute names across entities but with different meanings.
Access Pattern Modeling: Every design decision starts with the question, “How will I access this data?”

Single-Table Design in Practice

et’s break down a single-table schema using a blogging platform example, which includes Users, Posts, and Comments. These are classic relational entities with one-to-many relationships.

Example Table Layout

PK	SK	Entity Type	Attributes
USER#123	PROFILE	User	Name, Email
USER#123	POST#202	Post	Title, Content
USER#123	POST#203	Post	Title, Content
POST#202	COMMENT#301	Comment	CommentText, AuthorID
POST#202	COMMENT#302	Comment	CommentText, AuthorID

How Queries Work

Get User Profile:
Query PK = USER#123 and SK = PROFILE
→ Returns user info
Get User’s Posts:
Query PK = USER#123 and begins_with(SK, POST#)
→ Returns all posts by the user
Get Comments for a Post:
Query PK = POST#202 and begins_with(SK, COMMENT#)
→ Returns all comments for the post

This design co-locates related data using shared partition keys or by encoding relationships in the keys.

Common Conventions in Single-Table

1. Key Prefixing

Prefix partition and sort keys to identify entity types and relationships.

USER#<UserID>
POST#<PostID>
COMMENT#<CommentID>

This avoids collisions and makes queries easier to understand.

2. Overloading Attributes

Use generic attribute names like Data1, Data2, Meta, etc., but contextually populate them depending on the entity. This helps secondary indexes span multiple entity types.

Example:

Entity	Data1	Data2
User	Name	Email
Post	Title	CreatedAt
Comment	CommentText	AuthorID

3. Sparse Indexes

Indexes only include items that contain the indexed attribute. Use this to create GSIs (Global Secondary Indexes) that only apply to specific entities.

For instance, if only Posts have a PublishedAt attribute, then a GSI on that attribute will include only posts.

4. Materialized Aggregates

For frequent queries like “How many comments on a post?”, store a counter (CommentCount) with the post item and update it transactionally when comments are added or deleted. This avoids inefficient scans.

5. Type Attribute

Include an explicit Type attribute to identify the entity (User, Post, Comment) even though it’s encoded in the key. This helps with filtering and debugging.

Modeling Relationships without Joins

Relational DBs would model relationships with foreign keys and joins. DynamoDB does this by embedding data, denormalizing, or using access pattern-based key design.

One-to-many (User → Posts):
Use the user ID as part of the partition key for posts:
PK = USER#123, SK = POST#<PostID>

Many-to-one (Comments → Post):
Use the post ID as partition key for comments:
PK = POST#202, SK = COMMENT#<CommentID>

Many-to-many (Users ↔ Roles):
Create two items per relationship:

PK = USER#123, SK = ROLE#Admin
PK = ROLE#Admin, SK = USER#123

This allows queries in both directions.

Multi-Table Design

Philosophy

Multi-table design follows the traditional approach seen in relational databases: each entity type gets its own table. This makes the data model easier to reason about at a glance, especially for teams transitioning from SQL-based systems.

Advantages

Simplicity: Easier to understand for developers familiar with relational models
Less upfront design work: No need to pre-define complex access patterns
Clear entity boundaries: Separation helps with modular development and debugging

Disadvantages

Query Complexity: Retrieving related data across entities may require multiple queries and application-side joins
Cost: More queries can increase read units and latency
Management Overhead: Indexes and capacity planning have to be managed for each table

Example Multi-Table Schema

Using the same blog application:

Users Table

PK: UserID
Attributes: Name, Email, Bio

Posts Table

PK: PostID
Attributes: UserID (foreign key), Title, Content, CreatedAt

Comments Table

PK: CommentID
Attributes: PostID (foreign key), AuthorID, CommentText, CreatedAt

Fetching Related Data

To get all posts for a user:

Query the Posts table with a GSI on UserID
For each PostID, fetch comments from the Comments table

Each step involves separate queries. You’re also writing application logic to simulate joins that a relational database would do for you.

Single-Table vs Multi-Table: A Head-to-Head Comparison

Feature / Concern	Single-Table Design	Multi-Table Design
Ease of Onboarding	Steep learning curve	Easier for teams used to SQL
Query Performance	Highly optimized with minimal reads	May require multiple queries
Flexibility	Less flexible once design is set	More adaptable to changing requirements
Schema Evolution	Harder to refactor if access patterns change	Easier to change schemas independently
Write Complexity	More complex logic to manage keys and item types	Straightforward inserts and updates
Index Management	Fewer indexes needed, but require careful planning	Each table may need its own indexes
Cross-Entity Queries	Efficient via co-located data and GSIs	Requires app-side joins or multiple queries
Analytics & Reporting	Difficult unless you export to a warehouse	Easier to query for analytics
Scalability	Excellent if designed correctly	Also scalable, but harder to optimize globally

When to Use Which?

Single-Table Design: Best For…

Apps with clearly defined and stable access patterns
Use cases where minimizing latency and query cost is critical
Serverless backends (like with AWS Lambda) where network calls are expensive
Scenarios with tight coupling between related entities

Examples:

E-commerce sites (Users, Orders, Products, Shipments)
Multiplayer game servers (Players, Games, Scores)
Real-time messaging apps (Users, Messages, Conversations)

Multi-Table Design: Best For…

Projects that are still evolving their access patterns
Teams migrating from relational databases who want to ease the transition
Applications with complex analytics/reporting needs
Situations where clear separation of concern is preferred (e.g., microservices)

Examples:

Content management systems (CMS)
Blog or publishing platforms
Enterprise apps with admin panels and reporting dashboards

Applying DynamoDB Design

Step 1: Identify Your Access Patterns

Before modeling anything in DynamoDB, list every way your application needs to read and write data. This is the foundation of your design.

For example, in a blogging app:

Get a user profile by user ID
Get all posts by a user
Get all comments on a post
Get all posts with a specific tag (if tags are supported)

Step 2: Choose a Design Approach

If the access patterns are stable and predictable:

Go with single-table. You can optimize partition and sort keys, and reduce read/write costs.

If the patterns are volatile or evolving:

Use multi-table for now. It’s easier to change and decouple.

Step 3: Design the Keys and Indexes

For single-table:

Pick composite keys that encode relationships (e.g., USER#123, POST#202)
Decide what you’ll store in GSIs
Model one-to-many and many-to-many relationships with duplicated or linking items

For multi-table:

Normalize schema design like you would in SQL
Add GSIs for common query fields (e.g., UserID in Posts table)
Plan for batch operations across tables where needed

Step 4: Prototype Your Queries

Test real-world scenarios in the AWS Console or with tools like Dynobase or the NoSQL Workbench. Look at:

How many read capacity units (RCUs) each query consumes
The latency for each access pattern
The clarity of the item layout and key names

Mini Design Exercise

Imagine a simplified e-commerce app with:

Users
Orders
Products
Reviews

Single-Table Example

PK: USER#<UserID>, SK: PROFILE → User info
PK: USER#<UserID>, SK: ORDER#<OrderID> → Order
PK: PRODUCT#<ProductID>, SK: REVIEW#<ReviewID> → Reviews
PK: PRODUCT#<ProductID>, SK: DETAILS → Product info

Access patterns:

Get all orders for a user: PK = USER#<UserID> and SK begins_with ORDER#
Get all reviews for a product: PK = PRODUCT#<ProductID> and SK begins_with REVIEW#

You’ve co-located related items to support high-performance queries without joins.

Final Thoughts

DynamoDB forces you to think differently. It rewards those who plan access patterns early and challenges those who try to “build as they go.” Both single-table and multi-table designs have their place.

Single-table is ideal for optimized, predictable applications
Multi-table is better for flexibility, evolving schemas, and ease of onboarding

Don’t worry about getting it perfect on the first try. With tools like AWS CloudFormation, NoSQL Workbench, and DynamoDB Streams, refactoring is possible and sometimes necessary.

Non-Relational Databases and the Design Dilemma: Single Table or Multi Table?