Building An E-commerce Application Using The Single Table Design

Welcome to the 26th edition of Excelling With DynamoDB!

In this week’s issue, I'll demonstrate how to design a highly efficient database table for an e-commerce application using the single table design.

What is the Single Table Design?

Most database developers are accustomed to associating one table per entity.

Essentially, each data entity (customer, products, transactions) are typically all stored in its own table.

The single table design (I’ll refer to it as STD from now on) goes against this practice for one good reason.

In DynamoDB, you are charged for the capacity throughput and indexes of each table.

The more tables you have the more you will end up paying (especially if each table has several secondary indexes).

The Single Table Design instead encourages grouping all of your (related) data entities in one table.

You gain 2 benefits from this:

1. With a single table for your application, you pay much less (you pay 10x less if you have 10 tables instead).
2. Much lower latency when fetching multiple related items. (instead of fetching 3 related items from 3 different tables, you fetch them from one table, resulting in lower latency).

But how does this work in practice?

I'm glad you asked because we’ll actually design a single table for an e-commerce app next.

Building with The Single Table Design

You are tasked with designing the database for a simple e-commerce website.

Customers will visit your site, browse products, and place orders.

When an order is placed they will receive details of the transaction to confirm the order.

Basic, simple, and straightforward.

You need to support this core feature with your database design.

Here are the data entities our database will store:

• users
• orders
• transactions
• products
• payments
• shipping

A user has just placed an order for a product.

Let’s see how we could design this data and store it in our single table database.

There are several things I’d like to point out in the table data above.

We have 8 items (records) in this table. The first 7 items are stored in the same partition node as the user-101 partition.

These denote the orders, transactions, payments and shipping associated with a user.

Here is the flow:

• Each time a user signs up to our online store we write a user item to this database table (let’s call it “online-store”).
• The item we write requires a partition key which we write as a unique ID of the userID (generated randomly). The sort key will use the same userID. We also write their name, email, and address (as seen above).
• Each time a user places an order we write an item with the same partition key as that user (e.g. “user-101”). Then we generate a orderID which we define as the sort key (sk) as seen above. We can also store any relevant order info on that item.
• When the transaction is processed (when the customer pays for the product) we write a transaction item, with the user’s userID again as the partition key. (all related data use the same user’s userID — pk).
• The same logic applies to payments and shipping data.

Now when the store admin logs into the backend and needs to find all orders placed by a given user they can easily run the following pseudo-query:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk begins_with("order")        

This will fetch all items whose pk value match “user-101” and their sk value begins with the substring “order”.

In other words, all orders made by user-101.

We can do the same with all payments, transactions and shipments related to that user.

One really efficient design strategy we can use is adding the date of an order in its sk attribute.

For instance:

Here, we’ve added the date on which the order was made as part of the item’s sort key.

This enables a host of powerful filtering capabilities.

Let’s look at two of them.

The first is that it allows us to filter our query for a subset of items.

Say we want to fetch all orders made by a given user but only in the year of 2024, we can write the following pseudo-qeury:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk begins_with("order#2024")        

We can get more specific and fetch all of the user’s order for the month of October 2024 only:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk begins_with("order#2024-10")        

And of course we can go down one more level and get all orders made on a given day:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk begins_with("order#2024-10-01")        

The second powerful filtering capability this data model offers is range filtering.

We can specify a query to return all orders made between a certain date range.

Consider the following table:

Say we wish to view all orders made by a given user between the months of October and December in 2024.

We could write the following pseudo-query:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk between("order#2024-10-01", "order#2024-12-31")        

The between keyword lets us specify a range of string values that follow each other alphanumerically, as you can see above.

With these design pattern, you need only write order items to your table based on their year, month and date.

There are scores of further design patterns that enable similar, powerful and efficient queries.

To drive the point home on the advantage of the STD, we can enable an alternate design pattern and fetch all related data based on a user like so:

Instead of prefixing each item’s sk value as the entity type (like “order”, “transaction”, “shipping”, etc), we can use the following design.

In this alternate design pattern, we prefix each item with an identifier string such as BULK-101 or BULK-102 and retrieve groups of related data together.

If we wish to retrieve a grouping of related order information made by a given user, we can query our table like so:

Table: "online-store",
KeyConditionExpression: pk = "user-101" and sk begins_with("BULK-101")        

The bottom line here is twofold:

1. Understanding your data and your use case needs is paramount to efficient querying.
2. Use the appropriate design patterns based on your needs. For some use cases, this last pattern might be more useful, for others, the first pattern may be more efficient.

Summary of Benefits

As we have seen the STD allows us to efficiently query multiple related items in one single query rather than having to perform multiple queries on multiple tables, adding latency and higher costs.

With a multiple table approach, to retrieve the data related to a given user’s order requires us to query the users table, then the orders table, then the transactions table and finally the shipping table.

With the STD, we need only write one query and model our data accordingly.

This more efficient approach enables powerful filtering capabilities as we have seen.

With the single table design, the querying possibilities are only limited by your creativity, and understanding of your data.

?? My name is Uriel Bitton and I hope you learned something in this edition of Excelling With DynamoDB.

?? You can share the article with your network to help others learn as well.

?? If you enjoyed this you can subscribe to my newsletter on Medium to get my latest articles on DynamoDB and serverless by email.

?? my blog website is coming soon, so stay tuned for that.

?? I hope to see you in the next week's edition!