Single-table design methodology in DynamoDB - Step-by-Step Execution

Concept Flow - Single-table design methodology

Identify Entities

↓

Define Primary Key

↓

Design Sort Key Patterns

↓

Use Attributes for Item Types

↓

Query by Partition and Sort Key

↓

Fetch Related Items Together

↓

Optimize Access Patterns

↓

Single Table Holds All Data

This flow shows how to design a single DynamoDB table to hold multiple entity types by carefully choosing keys and attributes to support all queries.

Execution Sample

DynamoDB

PK = 'USER#123'
SK = 'PROFILE#123'
ItemType = 'UserProfile'
Attributes = {"Name": "Alice", "Age": 30}

This example shows storing a user profile item with partition key and sort key encoding the user ID and item type.

Execution Table

Step	Action	PK Value	SK Value	ItemType	Attributes	Result
1	Create user profile item	USER#123	PROFILE#123	UserProfile	{"Name": "Alice", "Age": 30}	Item stored
2	Create user order item	USER#123	ORDER#456	Order	{"OrderDate": "2024-06-01", "Total": 50}	Item stored
3	Query all items for USER#123	USER#123	Starts with ''	N/A	N/A	Returns profile and order items
4	Query only orders for USER#123	USER#123	Starts with 'ORDER#'	N/A	N/A	Returns only order items
5	Add product item	PRODUCT#789	METADATA#789	Product	{"Name": "Book", "Price": 15}	Item stored
6	Query product by PK	PRODUCT#789	METADATA#789	N/A	N/A	Returns product item
7	Exit	N/A	N/A	N/A	N/A	All items stored and queryable in one table

💡 All entities stored in one table with composite keys supporting queries.

Variable Tracker

Variable	Start	After 1	After 2	After 5	Final
PK	null	USER#123	USER#123	PRODUCT#789	PRODUCT#789
SK	null	PROFILE#123	ORDER#456	METADATA#789	METADATA#789
ItemType	null	UserProfile	Order	Product	Product
Attributes	{}	{"Name": "Alice", "Age": 30}	{"OrderDate": "2024-06-01", "Total": 50}	{"Name": "Book", "Price": 15}	{"Name": "Book", "Price": 15}

Key Moments - 2 Insights

Why do we use composite keys (PK and SK) instead of separate tables?

Using composite keys lets us store different entity types in one table and query related items efficiently, as shown in execution_table rows 3 and 4 where queries return multiple item types.

How does the sort key help in filtering items?

The sort key encodes item type or other info, so queries can filter by SK prefix (rows 3 and 4), returning only desired items without scanning the whole table.

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what items are returned when querying PK='USER#123'?

A Both user profile and order items

B Only the order item

C Only the user profile item

D No items

💡 Hint

Check the 'Result' column in row 3 of execution_table.

At which step is a product item added to the table?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Look at the 'Action' and 'ItemType' columns in execution_table rows.

If we changed the sort key of the order item to 'PROFILE#456', what would happen when querying orders by SK prefix 'ORDER#'?

A The order item would still be returned

B The order item would not be returned

C All items would be returned

D Query would fail

💡 Hint

Refer to how SK prefix filtering works in execution_table row 4.

Concept Snapshot

Single-table design in DynamoDB:
- Use one table for all entities
- PK identifies partition (e.g., user)
- SK encodes item type and sort order
- Attributes store item data
- Query by PK and SK prefixes to fetch related items
- Optimizes performance and reduces complexity

Full Transcript

Single-table design methodology in DynamoDB means storing multiple entity types in one table. We assign a partition key (PK) to group related items, like all data for one user. The sort key (SK) differentiates item types and orders them. For example, a user profile has SK 'PROFILE#123', and orders have SK starting with 'ORDER#'. This lets us query all user data or just orders efficiently. Attributes hold the actual data. This design reduces the need for multiple tables and complex joins, improving speed and simplicity.