To read more articles like this, visit my blog
What if I told you that your system only needs one table? If you are like me and have experience with SQL queries and schema design, I bet you are just as surprised as I am.
But this is a recommended way to model your database in NoSQL-type databases — especially in DynamoDB.
Today, I will explain how a system can be designed with a single table and the justifications behind this decision.
Tell Me More
Single-table database design is exactly like it sounds. You will have a single table for your entire system.
But how does it work? Different data have very different structures.
Yes, that's correct. Here comes the beauty of NoSQL. As the name suggests, this data storage type doesn’t need a strict schema. You can put anything you like. So, you can put User and Product side by side in the same collection.
Having a single-table design means you are putting all your data into the same table, and DynamoDB will not complain about that because it’s schema-less key-value pair storage.
But How Do I Get The Data Back?
This is the fun part. DynamoDB has a weird take on the concept of a primary key. You will have two keys to identify specific data:
Primary Key = Partition Key + Sort Key
To point to specific data, we can design our partition key so that it becomes easy and understandable to get them later.
Let’s take an example. We have an e-commerce system where we need to store the information on Products, Customers, Orders, etc. For storing this data, we can design our partition keys like this:
ENTITY_TYPE PARTITION_KEY (here, 1234 is just a random id)
Product -> PRODUCT#1234
Customer -> CUSTOMER#1234
Order -> ORDER#1234
So when you are querying your data when you want to get the Products, you can do it like so:
Where PK = PRODUCT#1234 -> PK means Primary Key
And if you need Customers, then it’s the same thing:
Where PK = CUSTOMER#1234
This way, you can split your data from multiple tables into a single table using the partition key concept.
What About Relations?
That’s a great question. In real life, we don’t just put individual and discrete pieces of data into our system. We have to get them back in a particular way. Let’s return to our previous example and try to understand what we are discussing.
Real-life scenario
A customer logs into the system and wants to see their orders.
An order needs to know which products are included.
An order needs to have the invoice item with it.
How can we effectively and efficiently solve these issues?
In this scenario, the concept of sort keys comes in handy. We said earlier that an item’s uniqueness depends on combining the primary and sort keys.
The primary key logically divides the data, whereas the sort key handles these conditions. Let’s model our Order and see how we can store an Order to get back those queries:
PRIMARY_KEY SORT_KEY OTHER_INFO
1. ORDER#1234 PRODUCT#1 ProductName,Price etc
2. ORDER#1234 INVOICE#1 InvoiceDate, PaymentInfo
3. ORDER#1234 CUSTOMER#1 CustomerName, ShippingAddress
So now if we want to get the products of a particular order, we can take advantage of the query function begins_with:
.
Where PK = ORDER#1234 and begins_with( SK, PRODUCT# )
Similarly, to get the Invoice list of a product, we can do this:
Where PK = ORDER#1234 and begins_with( SK, INVOICE# )
So we have to keep this in mind and design accordingly. To learn more about the more complex queries, you can check out this article.
What Are the Benefits of This Approach?
Well, there are several benefits. Let’s talk about some of them.
Single query to get multiple data
The main limitation of multi-table design with DynamoDB is that you can only query a single table at a time. If you keep your orders and ordered_produces in separate tables, you must query two tables to get all the details about an order.
But in a single-table design, you will do this:
where PK = ORDER#1234
This is a great advantage and the main reason behind having a single-table design.
Efficiency
In a single-table design, you can easily handle complex access patterns with the GSI (Global Secondary Index) concept. This way, your queries are more performant.
Understanding
A single-table design is easier to understand. It may look daunting at first, but if you have a single table, you don’t have to worry about many different primary keys and sort keys and how they work. You will have a single place to understand all of it.
So Is It All Sunshine and Roses?
No, not. There are multiple drawbacks to this approach. The first one is it has a very steep learning curve. Maybe we will talk about the drawbacks in a separate article.
I am still not convinced it’s the way to go, but it is a fascinating idea. Let me know your thoughts in the comments.
Have a great day!
Have something to say? Get in touch with me via LinkedIn or Personal Website
Top comments (4)
I dont k ow I mean I am not an engineer nor I have a degree but working on postgres as self taught. This may work if you have low count of related entity and your system need only few of this. But even for an e commerce I would think a user may have many invoice the invoice different order the order different product the peoduct different price and different verison of the price for a given version of thebproduct or timeline. All of this migjt get a very big table in no time and searching into it would easily become slow, even with index on (danno How they work on a no sql db) you will have slower writes (this happend on other db too but I am sure it is handled better on multiple tables).
Brother. This article is for NoSQL databases. Like DynamoDB
You've basically recreated tables without any of the useful tools and concepts like schematic evaluation we've developed over the last 60 years. You really need to learn SQL.
And you, my friend, needed to read the article a little more carefully.
One table design is not for SQL. This is for NoSQL databases. Specifically, DynamoDB.