DEV Community

Mohammad Faisal
Mohammad Faisal

Posted on • Originally published at betterprogramming.pub

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

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

And if you need Customers, then it’s the same thing:

Where PK = CUSTOMER#1234 
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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# )
Enter fullscreen mode Exit fullscreen mode

Similarly, to get the Invoice list of a product, we can do this:

Where PK = ORDER#1234 and begins_with( SK, INVOICE# )
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
devworkssimone profile image
DevWorksSimone • Edited

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).

Collapse
 
mohammadfaisal profile image
Mohammad Faisal

Brother. This article is for NoSQL databases. Like DynamoDB

Collapse
 
bobfrankston profile image
Bob Frankston

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.

Collapse
 
mohammadfaisal profile image
Mohammad Faisal

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.