DynamoDB Data Modeling (Single Table)
Tags: dynamodb, singletable, aws
Published: 2021-09-20 22:17:01

---
title: DynamoDB Data Modeling (Single Table)
published: true
description: 
tags: DynamoDB, Singletable, AWS
//cover_image: https://direct_url_to_image.jpg
---

**Source:** https://youtu.be/OfZgHXsYqNE 

### Steps of the process

- Draw an entity diagram
- Identify the relationships between entities (1:1, 1:N, N:M)
- List down all the access pattern for each entity
- Identity the primary key ( Hash + Sort) for each entity
- Identify the secondary indexes for additional access patterns if required

### Example | Project Management Tool

- SAAS Tool
- Multi-Tenant Support
- Manage Projects and Employees

### Step 01 - Draw and ERD

![Diagram ERD](https://i.postimg.cc/Bnw97jbX/1.png)

### Step 02 - Identify Relationships

![Relationships](https://i.postimg.cc/7ZLrN3xF/2.png)

### Step 03 - Identify Access Patterns

_**Organization**_

- Organization CRUD operations
- Find all the projects of an organization
- Find all the employees of and organization
- Find all projects and employees of an organization
- Find an organization by name

_**Project**_

- Project CRUD operations
- Find a project by name
- Find the employees assigned to a project
- Find agile projects
- Find fixed-bid projects
- Find on hold projects

_**Employee**_

- Employee CRUD operations
- Find all the projects an employee is part of
- Find an employee by name

_**Project-Employee**_

- Assign an employee to a project

### Step 04 - Identify Primary Key

![Identify primary key](https://i.postimg.cc/RFKk0GRH/3.png)

- Pk must be unique

### Example Queries

_Organization (org-id=1234)_

![Organization](https://i.postimg.cc/MGCkxgyw/4.png)

- **Organization CRUD operations** - PK=ORG#1234, SK=#METADATA·1234
- **Find all the projects of an organization** - PK=ORG#1234, SK begins_with(PRO#)
- **Find all the employees of an organization** - PK=ORG#1234, SK begins_with(EMP#)
- **Find both employees and projects** - PK=ORG#1234
- **Find organization by name** - Not satisfied yet

**Project (org-id=1234, project-id=100)**

![Project](https://i.postimg.cc/NFtW2TJP/5.png)

- **Project CRUD operations** - PK= ORG#1234, SK=PRO#agile#100
- **Find a project by name** - Not yet satisfied
- **Find the employees assigned to a project** - PK=ORG#1234#PRO#100
- **Find agile projects** - PK=ORG#1234, SK begins_with(PRO#agile)
- **Find fixed-bid projects** - PK=ORG#1234, SK begins_with(PRO#fixed-bid)
- **Find on-hold projects** - Not yet satisfied

_**Employee(org-id=1234, emp-id=300)**_

- **Employee CRUD operations** - PK= ORG#1234, SK EMP#300
- **Find all projects an employee is part of** - No yet satisfied
- **Find all employees by name** - No yet satisfied

### Step 05 - Identify Secondary Indexes

- Find all projects an employee is part of - **Use an inverted index**

![Project](https://i.postimg.cc/XNcMSHXM/6.png)

Query on GSI - **PK = ORG#1234#EMP#300**

- Find all organizations, projects, employees by name - GSI Overloading

![Filter by name](https://i.postimg.cc/dt4zDh84/7.png)

Find by org name - PK=ORG#1234, filterName=ORG#HappyInc

Find by emp name - PK=ORG#1234, filterName=EMP#Manoj

- Find On-hold projects - Use a Sparse Index

![On hold project ](https://i.postimg.cc/0jChYQh5/8.png)

**You can Query or scan the GSI to find all the on-hold projects**

### Using Filter Conditions

- These are used after the run queries and is necessary to add an extra filter with fields that aren't indexes nor keys.
- A filter query is applied after the query finished, but before the results are returned. A query consumes the same amount of read capacity, it not been affected if a filter condition exist
- Use filter conditions if the **secondary indexes cost more than the filter conditions** due to low query velocity or frequency.

### Inverted Index

![Inverted index](https://i.postimg.cc/KvZ6ZPg1/9.png)

Query on GSI - **PK=ORG#1234#EMP#300**

The reason to use an inverted index is because of the value to filter that I need is the SK value, so, I can't use a query method without specified a PK, in this way we can swap the PK and SK to become SK in PK and now is possible to do the query.

**Note:** It's necessary create an index table

### GSI

![GSI](https://i.postimg.cc/wjMnN92H/10.png)

It's used when you need to make a search using a field different to SK to complement the search keys, so, you can create a GSI to make possible it query, in above example, the GSI is created for enable the filter by name query using PK + a new field called Data

- Find by org name - PK=ORG1234, Data=ORG#HappyInc
- Find by emp name - PK=ORG#1234, Data=EMP#Manoj

**Note:** It's necessary create an index table