Single-table design methodology in DynamoDB

Introduction

Single-table design helps store different types of data in one table. It makes reading and writing data faster and simpler.

When you want to keep all related data together for quick access.

When your app needs to fetch multiple related items in one request.

When you want to reduce the number of database calls to save time and cost.

When your data has different types but shares common access patterns.

When you want to simplify your database structure for easier maintenance.

Syntax

DynamoDB

PartitionKey: string
SortKey: string
Attributes: map of key-value pairs

Use PartitionKey and SortKey to organize and query data efficiently.

The PartitionKey groups related items together.

The SortKey orders items within the same PartitionKey.

Examples

This stores a user profile with user ID 123.

DynamoDB

PartitionKey: USER#123
SortKey: PROFILE
Attributes: {name: "Alice", age: 30}

This stores an order for user 123 with order ID 456.

DynamoDB

PartitionKey: USER#123
SortKey: ORDER#456
Attributes: {item: "Book", price: 15}

Another order for the same user, stored in the same table.

DynamoDB

PartitionKey: USER#123
SortKey: ORDER#457
Attributes: {item: "Pen", price: 5}

Sample Program

This example creates a single table to store a user profile and their orders. Then it fetches all data for that user in one query.

DynamoDB

CREATE TABLE SingleTable (
  PartitionKey STRING,
  SortKey STRING,
  Attributes MAP<STRING, STRING>,
  PRIMARY KEY (PartitionKey, SortKey)
);

-- Insert user profile
INSERT INTO SingleTable VALUES ('USER#123', 'PROFILE', {'name': 'Alice', 'age': '30'});

-- Insert orders
INSERT INTO SingleTable VALUES ('USER#123', 'ORDER#456', {'item': 'Book', 'price': '15'});
INSERT INTO SingleTable VALUES ('USER#123', 'ORDER#457', {'item': 'Pen', 'price': '5'});

-- Query all data for user 123
SELECT * FROM SingleTable WHERE PartitionKey = 'USER#123';

Important Notes

Design your PartitionKey and SortKey carefully to match how your app accesses data.

Single-table design can be tricky at first but improves performance a lot.

Use clear prefixes like USER# or ORDER# to keep data organized.

Summary

Single-table design stores different data types in one table using keys.

It helps fetch related data quickly with fewer queries.

Good key design is important for easy and fast data access.