CMY304
Data modeling with Amazon DynamoDB
Alex DeBrie
Engineering Manager
Serverless, Inc.
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Agenda
What is Amazon DynamoDB?
Key concepts
Data modeling strategies
Alex DeBrie
Engineering Manager, Serverless, Inc.
DynamoDBGuide.com
DynamoDBBook.com
@alexbdebrie
alexdebrie.com
Related breakouts
DAT301-R: Data modeling with Amazon DynamoDB in 60 minutes
DAT325: Amazon DynamoDB: Under the hood of a hyperscale database
DAT403-R: Amazon DynamoDB deep dive: Advanced design patterns
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
What is DynamoDB?
NoSQL database
Fully managed by AWS
HTTPS with AWS Identity and Access Management auth
Fast, consistent performance as it scales
Hyperscale
Hyper-ephemeral compute
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Concepts
• Table
• Item
• Primary key
• Attributes
Table
Item
Primary key
Attributes
Primary key
• Simple primary key (partition key)
• Composite primary key (partition key + sort key)
Simple primary key
Composite primary key
API actions
• Item-based actions
Item-based actions—writing, updating, deleting
Item-based actions—writing, updating, deleting
Must
provide
entire
primary
key
API actions
• Item-based actions
• Query
Query
Query
Must
provide
partition
key
May
provide
sort key
conditions
API actions
• Item-based actions
• Query
• Scan
Scan
Scan
Avoid!
Expensive
at scale
Secondary indexes
Secondary indexes
Secondary indexes
Secondary indexes
Secondary indexes
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Basics
1. Start with an ERD
2. Define your access patterns
3. Design your primary key & secondary indexes
Forget your relational experience
• Normalization
• JOINs
• One entity type per table
Setup
• E-commerce store
• Users make orders
• An order may have many items
1. Create your ERD
E-commerce store
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
2. Define your access patterns
Identify access patterns
1. Get user profile
2. Get orders for user
3. Get single order and order items
4. Get orders for user by status
5. Get open orders
3. Design your primary keys & secondary indexes
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address
Order
Order Item
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
Denormalization + document types
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address N/A N/A
Order
Order Item
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
One-to-many: Sort keys
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address N/A N/A
Order USER#<username> ORDER#<orderId>
Order Item
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address N/A N/A
Order USER#<username> ORDER#<orderId>
Order Item
Query:
“PK = USER#alexdebrie AND BEGINS_WITH(SK, ‘ORDER#’)”
One-to-many: Sort keys
“PK = USER#alexdebrie AND BEGINS_WITH(SK, ‘ORDER#’)”
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
users orders
1 *
username string username string
1
1
full_name string order_id string
email string user_address string
date_of_birth string status varchar
created_at string created_at varchar
user_address order_items
username string item_id uuid
*
street_address string order_id uuid
*
postal_code string product_name string
state string price int
country_code int status string
quantity int
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address N/A N/A
Order USER#<username> ORDER#<orderId>
Order Item ITEM#<itemId> ORDER#<orderId>
Entities
PK SK
User USER#<username> #PROFILE#<username>
User Address N/A N/A
Order USER#<username> ORDER#<orderId>
Order Item ITEM#<itemId> ORDER#<orderId>
Inverted index
Inverted index
Inverted index
One-to-many relationship patterns
1. Attribute (list or map)
2. Primary key + query
3. Secondary index + query
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
Filtering
SELECT * FROM ORDERS
WHERE USERNAME = ‘alexdebrie’
AND ORDERDATE > GETDATE() - 14
Filter expressions
Filter expressions
Filter expressions
Filter expressions
Query
Must
provide
partition
key
May
provide
sort key
conditions
Filter expressions
Filter expressions
Filter expressions
Filter expressions
Filter expressions
Filter expressions
Filter expressions
Filtering access patterns
1. Get orders for user
2. Get orders by status for user
3. Get open orders
Filtering access patterns
1. Get orders for user
2. Get orders by status for user
3. Get open orders
Filtering access patterns
1. Get orders for user SELECT * FROM ORDERS
WHERE USERNAME = ‘alexdebrie’
2. Get orders by status for user
3. Get open orders
Filtering access patterns
“PK = USER#alexdebrie AND BEGINS_WITH(SK, ‘ORDER#’)”
Filtering patterns: Primary key
“PK = USER#alexdebrie AND BEGINS_WITH(SK, ‘ORDER#’)”
Filtering access patterns
1. Get orders for user
2. Get orders by status for user
3. Get open orders
Filtering access patterns
1. Get orders for user
SELECT * FROM ORDERS
WHERE USERNAME = ‘alexdebrie’
2. Get orders by status for user
AND STATUS = ‘SHIPPED’
3. Get open orders
Filtering access pattern
Filtering access pattern
Filtering access pattern
Filtering patterns: Composite sort key
Filtering access patterns
1. Get orders for user
SELECT * FROM ORDERS
WHERE USERNAME = ‘alexdebrie’
2. Get orders by status for user
AND STATUS = ‘SHIPPED’
3. Get open orders
Filtering patterns: Composite sort key
“PK = USER#alexdebrie AND BEGINS_WITH(OrderStatusDate, ‘SHIPPED#’)”
Filtering patterns: Composite sort key
Filtering patterns: Composite sort key
“PK = USER#alexdebrie AND BEGINS_WITH(OrderStatusDate, ‘SHIPPED#’)”
Filtering access patterns
1. Get orders for user
2. Get orders by status for user
3. Get open orders
Filtering access patterns
1. Get orders for user
2. Get orders by status for user
3. Get open orders
Filtering access patterns
1. Get orders for user
SELECT * FROM ORDERS
WHERE STATUS = ‘PLACED’
2. Get orders by status for user
3. Get open orders
Filtering access patterns: Sparse index
Filtering access patterns: Sparse index
Filtering access patterns: Sparse index
Filtering patterns
1. Primary key
2. Composite sort key
3. Sparse index
Thank you!
Alex DeBrie
@alexbdebrie
dynamodbbook.com
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
© 2019, Amazon Web Services, Inc. or its affiliates. All rights reserved.
