press any key or tap to skip

>vs slavchev's cyberspace

Single-table design in DynamoDB

by Veselin Slavchev database DynamoDB

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. 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:
  1. get unlocked recipe ids for user
  2. get batchItem recipes by the recipe ids
  3. 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: