Getting Started with DynamoDB single table design
In the world of NoSQL databases, Amazon Web Services (AWS) DynamoDB stands as a powerful and highly scalable option for storing and retrieving data. When it comes to structuring your data within DynamoDB, one design pattern is particularly popularity — the Single Table Design. In this blog post, we will explore the concept of Single Table Design, compare it with Multi-Table Design and showcase some real-world examples to help you get started. Lets get into it!!
What is DynamoDB?
Amazon DynamoDB is a fully managed NoSQL database service offered by AWS. It is designed to provide high availability and seamless scalability, making it an excellent choice for applications with rapidly changing workloads or unpredictable traffic patterns. DynamoDB is known for its low latency, high performance, and ease of use. It offers features like automatic scaling, data replication, and encryption at rest and in transit. Feel free to click the link below if you want to do some more reading on this DB Service.
https://aws.amazon.com/dynamodb/
What is Single Table Design?
Single Table Design, as the name suggests, is a design philosophy that encourages storing all your data in a single DynamoDB table, even if your application has various data entities or relationships. In essence, it eliminates the need for creating multiple tables ( and JOINS ), as is commonly required in traditional relational database patterns. Instead, it relies on a flexible schema and leverages partition and sort keys to organize and access data efficiently.
Hows it different to a traditional Multi-Table design?
In a multi-table design, each data entity or relationship is stored in a separate table, using a primary and foreign keys to represent unique records and relationships between data. This approach is how traditional relational databases work. It provides strong data isolation and can be more intuitive for developers coming from an SQL background. Lets take a look at a concrete example. Say we have an application that takes customer orders. You would typically design your data around a Customer table using a Primary Key (PK) called CustomerID and an Orders table using a Primary Key called OrderID. As a single Customer could place multiple orders, your Orders table would also contain an association to the CustomerID in the Customer table. This is called a Foreign key (FK) highlighted blue in our table below.
Pretty simple right!. If i want to list all the orders for a particular customer I can looking the CustomerID from the Customer table and then query the Orders table with something like;
select * FROM Orders WHERE CustomerID = {CustomerID}Single Table Design
Single Table Design, on the other hand, consolidates your data into the logical access patterns under a combination of Partition and Sort Key . While it may seem unconventional at first, so lets take a look at the above example transposed it onto a single table design.
The first thing you might notice is we have a Partition Key (PK) and Sort Key (SK) in our table. We refer to this as a composite key. This composite key is important as it determines how we store our data in the table.
More specifically, to read and write data to the table, DynamoDB uses the value of the partition key as input to an internal hash function. The output from the hash function determines the partition in which the item will be stored or retrieved. When using a Composite Key (PK & SK) all the items with the same partition key value are located physically close together, ordered by sort key value. This is very efficient and what makes DynamoDB super fast are very scalable. If your interested in diving a little deeper on this concept, have a read of the link below;
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.Partitions.html
So coming back to our example if we wanted to store order information with customer data we could use the orderID as the Sort Key value which would place this related data next to the customer information in the same partition.
The next thing you will notice is our PK and SK attributes are using a flexible schema, comprised of a character prefix, hash and then a unique identifier. This may look odd, but lets take a min to pick this apart.
The character prefix is used to distinguish data types from each other. By using a flexible schema like this we can store Customer data and Order data together but partitioned separately. In our example we use C# for Customer data and O# for order data. The side benefit of leveraging a nomenclature like this is we can standardize our query language so a single query can be used for different data types. E.G
`Selecting Customer Records`
select * WHERE PK = 'C#00001' and SK = 'C#00001' # Customer Record
`Selecting Order Records`
select * WHERE PK = 'O#1111' and SK = 'O#1111' # Customer Records
Note: You don’t need to use the prefix approach described above, but you should use a consistent approach that is easy to understand and partitions your data in ways that isolate data types.
The remaining portion of the PK/SK value is the identifier of that record. Much like what you would see in the Primary Key of a RDBMS table design.
So lets extend our thinking slightly to help cement this concept and showcase its power. Take a look at the table data below;
We have Customer records and Order Records stored together in the same table. Within order O#01111 we have Order details, Product Details and Customer Details. We can now exploit our flexible schema to return different data elements using relatively simple query language. For example;
`Selecting All records associated with an Order`
SELECT * FROM "single-table" WHERE "PK" = 'O#01111'Results in the following data returned;
`Selecting just the Order details`
SELECT * FROM "single-table" WHERE "PK" = 'O#01111' AND "SK" = 'O#01111'Results in the following data returned;
or finally this which returns all the products attached to a particular order
`Selecting All Products within an Order`
SELECT * FROM "single-table" WHERE "PK" = 'O#01111' AND begins_with("SK", 'P#')which results in
Pretty cool huh?
Finally, those eagle eyed readers may have spotted an additional attribute in my response data. I introduced the EntityType attribute. This is optional but makes good sense for two main reasons. 1. When you start thinking about how your applications will use the data this is an effective way to retrieve larger datasets and filter at an application layer based on the EntityType attribute and 2. As the data model gets more complex, you can use EntityType as a user friendly way to identify the different data objects contained within your table. Remember, you may not be the person that maintain this in the future, so making things obvious for others is just good practice!!
Why Single Table Design is Good
1. Makes you think about access patterns
As you’ve seen from the trivial example above, storing different data objects in a table is pretty efficient, I only need to look in 1 place, but equally the structures can get complex and confusing as the data model gets bigger. That’s why you need to think about access patterns before you start. Actually single table design almost enforces you to think about all the ways you might access the data before you can model it and I think this is a good thing. By thinking about the access patterns as a initial step, your table will likely be very efficient and partitioned according to actual real world use cases, which makes DynamoDB very fast.
I tend to start with a simple table like the one below and add to it as new access patterns emerge;
Access Pattern — The query in conversational language
Table / GSI / LSI — How the data will be partitioned
Key Condition — The attributes you need in the PK and SK to fulfill your Access Pattern
Filter Expressions — Any additional logic required to filter the results to fulfill your Access Pattern
This approach will encourage you to record the access patterns no matter how complex and . In the example above our patterns are very simple and therefore our access patterns are not complex, however as we identify new patterns we may need to incorporate other aspects like Global or Local Indexes (GSI/LSI) and filter expressions. Dont worry about those fields just yet we will tackle some of those concepts a little later.
2. Query Efficiency
Single Table Design excels in terms of query efficiency. With the right use of composite keys and secondary indexes, you can efficiently retrieve data with minimal de-normalisation or subsequent requests. This approach simplifies complex queries, reduces the need for JOIN operations, and can lead to substantial cost savings as you pay per request.
2. Scales Better
As your application grows, Single Table Design scales seamlessly. DynamoDB’s automatic partitioning ensures even distribution of data, preventing hot partitions that can impact performance. You can handle massive amounts of data and requests without worrying about table or schema changes.
When Not to Use Single Table Design
While Single Table Design offers numerous advantages, it may not be suitable for all scenarios. Ill dig into this in more detail in a followup post but for now here are 2 primary examples of when a single Table Design is not ideal.
1. GraphQL
If your application relies heavily on GraphQL, which often involves complex queries with varying data structures, a single-table design may be overly complex and not give you any of the benefits.
2. Analytics Use Cases
For analytics or reporting-heavy use cases, where you need to perform complex aggregations or scan large data sets, DynamoDB may not be the best choice. Other databases like Amazon Redshift or Athena are better suited for such tasks.
Conclusion
AWS DynamoDB’s Single Table Design is a powerful paradigm shift in NoSQL database design. It promotes query efficiency, simplifies data modeling, and enables seamless scaling. When used appropriately, it can lead to significant improvements in application performance and cost savings. However, it’s essential to evaluate your specific use case to determine whether Single Table Design aligns with your application’s needs. For many applications, embracing this design philosophy can unlock the true potential of DynamoDB and help you build more efficient and scalable systems.