---
title: "Single-table design in DynamoDB"
author: "Veselin Slavchev"
date: 2024-09-25
tags: ["database", "DynamoDB"]
description: "An introduction to single-table design in DynamoDB: modelling entities and their relationships with composite partition and sort keys in one table."
url: https://vsslavchev.com/blog/single-table-design/
---

# Single-table design in DynamoDB

**In short:** List your access patterns first, then give every entity a partition key and sort key like USER#123 / HOUSE#456 so related items share a partition and come back in one query, with begins_with on the sort key and an overloaded GSI for the rest. The worked example is the schema of SpeedCook.

Single-table design is a way to model your data so that all your entities and their relationships are in a single table in DynamoDB. This allows querying data in a single request and avoiding joins as they are not supported by DynamoDB, and has other cost and performance benefits.

To find an object in DB we need to query it using both its PK (partition key) and SK (sort key).
` PK: "USER#123"
SK: "USER#123" `
We must specify a sort key, so here both keys have the same value. This is the case for simple entities only. The id of the user is prepended by the type of entity and a '#' to delimit them.

Now if we want to specify the relation that a user can have a house, we can do that in the same table like so:
` PK: "USER#123"
SK: "HOUSE#456" `

This enables us to find the house if we know both user id and house id. Also to find all houses owned by the user only using their id:
` query conditions: PK = "USER#123" and SK beginsWith "HOUSE#" `

We can expand the house entry to contain info on the state of the house:
` PK: "USER#123"
SK: "HOUSE#IN_CONSTRUCTION#456" `
This way we can query all houses of this user, or only those in a specific state.
` query conditions: PK = "USER#123" and SK beginsWith "HOUSE#FINISHED#" `
This stacking can be done multiple times to allow range queries on different attributes. A drawback here is that to get a house by user id and house id, we also need to know its state. This is why we need to know our access patterns before making the schema.

Another technique to allow flexibility of queries is adding more indexes. DynamoDB supports multiple GSIs (global secondary indexes) per table. For example:
` GSI1 PK: "CITY#Sofia"
GSI1 SK: "HOUSE#456" `
We can now query all houses in a city without knowing the user id. The index stores its own copy of every item it contains, so it takes up extra storage. Which fields are copied (projected) is chosen when the index is created: only the PK and SK, a selection of fields, or all of them.

GSI overloading is a trick to serve more access patterns than you have global secondary indexes for, or than the per-table limit allows. It's good practice to keep the GSIs to a minimum to save on storage and write costs. We can do this by using the same GSI with a different PK and SK for each entity.

To demonstrate this design process we can go through the steps as I did for [SpeedCook](https://www.speedcook.org/). The entities are:

- Users
- Recipes
- Unlocked recipes
- Cooked meals

And the properties and relations they have:

- Recipes have details
- Users have details
- User has many unlocked recipes
- User has many cooked meals

The rough functionality we want is:

- CRUD users and recipes
- Unlock recipe for user
- List unlocked recipes for user
- Record cooked meals and see their history

Based on the above we need to come up with the access patterns for each entity:

- User
  - Get user profile
- Recipe
  - Get single recipe
  - Get all recipes that can be cooked: open + unlocked
  - Get all locked recipes in a dietary category when choosing what to unlock
- Unlocked recipe
  - Unlock a locked recipe (unique, only once for a single user)
  - Get all unlocked recipes for user
- Cooked meal
  - Complete a cooking of a recipe by a user, non-unique - a user can cook the same recipe multiple times
  - See history of completed meals

Now we have everything we need to specify our schema. For each access pattern we specify if it reads or writes or both, if the target is the main index or a global secondary one, what is the query and a note on how it will be used:

| Access pattern | operation | target | PK / SK | Notes |
| --- | --- | --- | --- | --- |
| User CRUD | R/W | main | PK USER#user_id  SK USER#user_id | Unique entry |
| Get single recipe | R/W | main | PK RECIPE#recipe_id  SK RECIPE#recipe_id | Unique entry; non-key attribs: name, category, accessLevel, cookingTime, imageName, ingredients, spices, instructions |
| Get unlocked recipe ids for user, order by time of unlocking | R | main | PK USER#user_id  SK begins_with(UNLOCK_TIME#) | |
| Get all recipes that can be cooked in one or more categories: unlocked for user + open | R | GSI1 | PK ACCESS#access_type  SK CATEGORY#category_id#recipe_id | three steps:  - get unlocked recipe ids for user  - get batchItem recipes by the recipe ids  - get all open recipes |
| Get all locked (for user) recipes in a dietary category when choosing what to unlock | R | step 1: GSI1, step 2: main | step 1: PK ACCESS#access_type  SK CATEGORY#category_id#recipe_id,  step 2:  PK USER#user_id  SK begins_with(UNLOCK_TIME#) | two steps:  1. get all locked (attainable) recipes  2. get all unlocked recipe ids for user and remove them from the list of locked ones |
| Unlock recipe for user | W | main | PK USER#user_id  SK UNLOCK_TIME#unlock_time | |
| Complete a cooking of a recipe by a user | W | main | PK USER#user_id  SK COOKED_TIME#cooked_time | Not unique: the time keeps repeated cookings of the same recipe apart |
| See history of completed meals, order by time of cooking | R | main | PK USER#user_id  SK begins_with(COOKED_TIME#) | |

References:

- Dynobase. “DynamoDB single table design made easy.” Accessed 25 Sept, 2024. [https://dynobase.dev/dynamodb-single-table-design-tool](https://dynobase.dev/dynamodb-single-table-design-tool)
- AWS. “Data modelling foundations in DynamoDB.” Accessed 25 Sept, 2024. [https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/data-modeling-foundations.html](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/data-modeling-foundations.html)
- AWS. “Best practices for using sort keys to organize data in DynamoDB.” Accessed 25 Sept, 2024. [https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-sort-keys.html](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-sort-keys.html)