What If You Only Need One Table for Your Entire Database System?

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 to make it 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