DynamoDB Table Design Principles - One-To-Many Relationships in NoSQL

Abstract

In Relational Database we used to have single Entity per Table, build relationships between them and assign constrains like Foreign Keys. In NoSQL DynamoDB world this became an Antipattern, many engineers follow same concept introducing multiple DynamoDB Table per each Entity type. Also in NoSQL DynamoDB world there are no JOIN operations and this is a BIG challenge when switching the mindset from RDBMS.

There are several technics to design 1-to-many relation in the DynamoDB, in this post we will take a deep dive view on the following patterns:

• Embedded Collections
• Single Table

Target DynamoDB Table Structure

NoSQL DynamoDB Table Structure is generated using MultiCloud Diagrams Framework

Entity-Relationship-Diagram EDR

erDiagram
  USERS ||--o{ USER_ADDRESS: has
  USERS ||--o{ ORDERS: creates
  ORDERS ||--o{ ORDER_ITEMS: has
  USERS {
    string username
    string full_name
    string email
    string date_of_birth
    string created_at
  }
  USER_ADDRESS {
    string username
    string street_address
    string postal_code
    string state
    int country_code
  }
  ORDERS {
    string username
    string order_id
    string user_address
    string created_at
  }
  ORDER_ITEMS {
    uuid item_id
    string order_id
    string product_name
    int price
    string status
    int quality
  }

Define Access Patterns

Once Entities are defined we need to define Access Pattern. Usually they are defined according to UI navigation components or other backend systems that are accessing our storage level.

ID	Access Pattern
A1	Get user profile
A2	Get orders for user
A3	Get single order and order items
A4	Get orders for user by status
A5	Get open orders

Table design

Define the Core Entity in the Relationships

There are 4 Entities in our EDR, we need to choose Core Entity - User is the best candidate.

Design your primary keys & secondary indexes

• Compound Key consists of PK and SK naming, should be standardized, because we will store multiple different entities in the same table so accessed keys should follow same naming convention

• Because a table stores not only a single entity type - we will add prefix with Entity type USER#ID to PK and #PROFILE#id to SK

• This prevents from a possible collision of same ID repeatable in different entities.

Entity Chart

Entity	PK	SK
User	User#username	#PROFILE#username

One-To-Many relationships mapping

User-to-Addresses RelationShip

Technic #1: Using Embedded Collections

users -> user_address

Since there are no access patterns to fetch user by Address, or fetch the Address directly,

Number of single user addresses is bounded (it is a small number).

We can denormalize the Addresses and inject them into every User Item as a dedicated attribute of Collection (Map or List).

Denormalize Data and put into a document as Embedded Collection

Entity Chart

Entity	PK	SK
User	User#username	#PROFILE#username
UserAddress	N/A	N/A

ID	Access Pattern	Access BY	Key Condition
A1	Get user profile	username	PK = USER#alex AND BEGINS_WITH(SK, ‘PROFILE’)”

User-to-Orders RelationShip

Technic #2: Single Table for Entities

users -> orders is district from previous example

Non-bounded number of Orders for a User

To distinguish Orders we populate compound key with ORDER#orderid for SK and USER#userid for PK

Entity Chart

Entity	PK	SK
User	User#username	#PROFILE#username
UserAddress	N/A	N/A
Order	USER#username	ORDER#orderid

Now different Entity types will have different attributes.

Also, Users & Orders - have same PK they will be within the same partition in the storage.

Query

"PK = USER#alex AND BEGINS_WITH(SK, 'ORDER#')"

ID	Access Pattern	Access BY	Key Condition
A1	Get user profile	username	PK = USER#alex AND BEGINS_WITH(SK, ‘PROFILE’)”
A2	Get orders for user	BY username	PK = USER#alex AND BEGINS_WITH(SK, ‘ORDER#’)”
A3	Get single order and order items	GSI: orderid	SK = ORDER#orderid

Order-to-OrderItems RelationShip

orders -> order_items

We can not reuse same technic because Orders are already a one part of other relationship to users.

Now we will introduce PK ITEM#itemid and SK ORDER#orderid

Entity Chart

Entity	PK	SK
User	User#username	#PROFILE#username
UserAddress	N/A	N/A
Order	USER#username	ORDER#orderid
Order Item	ITEM#itemid	ORDER#orderid

Important note that SK is the same for Order and OrderItems

Technic #3: Use Inverted Index

For composite Key we will flip PK and SK and introduce the GSI, creating the inverted index.

Query the Index

"SK = ORDER#orderid" - will return the ORDER Entity and all ORDER ITEMS - this is analogue of JOIN SQL operation on FK ORDERID.

ID	Access Pattern	Access BY	Key Condition
A1	Get user profile	username	PK = USER#alex AND BEGINS_WITH(SK, ‘PROFILE’)”
A2	Get orders for user	BY username	PK = USER#alex AND BEGINS_WITH(SK, ‘ORDER#’)”
A3	Get single order and order items	GSI: orderid	SK = ORDER#orderid

We will complete last 2 Access Patterns in next Post, when will review in details Filter Expressions in DynamoDB.

References (Links)

• DynamoDB Table Design Principles - NoSQL Modeling
• DynamoDB Table Design Principles - Filtering
• GitHub Sample CloudFormation and NoSQL Workbench exports for One-To-Many
• Alex Debrie Talk on re:Invent 2019 Data modeling with Amazon DynamoDB (CMY304)
• Multicloud Diagrams