DynamoDB — Everything You Need To Know About Single Table Design

Published in

Duda

7 min readJan 27, 2022

DynamoDB is a serverless database with automatic scaling and maintenance by AWS. It is a fully-managed NoSQL key-value database.

Why DynamoDB? Let’s take a look at the advantages.

Schema-less

You don’t need to define the schema of the table at the database level. The schema will be defined at the application level, but the database doesn’t enforce it

Fixed performance at any scale

DynamoDB offers the same performance time regardless of the amount of data. On the other hand, a SQL database’s performance will degrade exponentially with a linear growth of data because of the joins being done between tables

On-demand billing

Pay per request pricing for read and write requests, so that you only pay for what you use

Easy integration with other AWS services

Common options are AWS Lambda, used to define an action to happen each time an item is persisted to the database, or to use an IAM role to manage access to the table

Although DynamoDB provides many great advantages, those advantages come at a cost. There is no native support in joining two tables during queries, and therefore data modeling may be less intuitive compared to relational databases.

Single table design to the rescue

Fortunately, you can overcome the lack of joins by using a design pattern named single table design. To get a better understanding of single table design let’s look at the following example:

As you may know, Duda provides a platform to build websites. Duda’s users’ most requested feature was the membership feature — site owners wanted the ability to create a website with pages that are restricted to members only, so only signed up site visitors could access those pages.

Let’s take a look at how we created this feature using single table design.

A site can have three states:

Site with no membership
Site with membership, but with no members — meaning some pages are restricted to members only, but no one has signed up to the site yet
Site with membership and with members

*Membership feature’s schema in a relational database*

Here is the membership feature’s schema in DynamoDB [#1 draft]

For clarity’s sake, let’s first define a few DynamoDB terms:

Table — a collection of related data

Item — similar to a record in a relational database, an item represents a single record

Primary Key — the key in this key-value database, which must be unique for each item and must be included when persisting data to the database. The primary key can be a simple key (composed of a partition key only) or a composite key (composed of a partition key and a sort key)

Here is the membership feature’s schema in DynamoDB, using composite primary key [#2 draft]

Attributes — similar to columns in a relational database, attributes store data about the item. Attributes don’t need to be defined on the database level and can be different for each item in the same table. Unlike relational databases, you can’t query or update the data using the attributes, but only using the primary key
Item Collection — a group of items that have the same partition key value

The table we’ve created so far, enables us to query it using only a site alias and a member uuid. But in reality we need to support more access patterns to the data, for example we need to get a member by an email. To solve this, DynamoDB has introduced two types of indices:

Global Secondary Index — GSI copies your data, where the value of the partition key will be an attribute of the base table

Local Secondary Index — LSI copies your data with the same partition key as the base table, but a different sort key

Due to the fact that data is mainly fetched by specifying the primary key, which can’t be changed after table creation, it is important to know the access patterns in advance in order to model your data.

At this stage, the table represents the relation between sites and members, but the membership representation is still missing (see Membership feature’s schema in a relational database diagram). In order to add the membership entity to the table, we will take advantage of the fact that DynamoDB is schema-less to put all entities in the same table, even though their attributes are different.

Here is the final version of the membership feature’s schema in DynamoDB

*Membership feature’s schema in DynamoDB [final version]*

The above schema is the actual schema used in Duda. Let’s take a closer look at the following conventions:

Both the partition key and sort key are strings starting with an uppercase prefix, followed by a delimiter and then the value. This enables putting multiple entities in the same table — the member entity has a sort key that starts with MEMBER# , while the membership entity has a sort key that starts with METADATA#
The member entity and the membership entity don’t have the same attributes. This is possible because DynamoDB is a schema-less database.

Let’s see some code

Now that we understand the theory, let’s look at it in action on a Java application.

When I started to implement the membership feature, I chose to use AWS SDK for Java with DynamoDBMapper, allowing us to map client-side classes to DynamoDB tables.

First, let’s create a composite key that will represent both the partition key and the sort key.

Composite Key

Then let’s create an entity with a primary key, which will be the base entity of both a member and a membership.

Entity with Primary Key

The partition key is annotated with a hash key annotation, and the sort key is annotated with a range key annotation. I defined both keys as strings in the membership table on AWS, so a conversion needs to be done from the Composite Key to a string. In order to do that, we need to create a converter:

Composite Key Converter

Now we can create the membership entity and the member entity:

Membership Item

Member Item

Again, you can see that since the email is not a supported data type on DynamoDB, you need to convert it to a supported type by creating a converter similar to the Composite Key Converter. Also, you can notice that the email is defined as the sort key of the LSI, named ByEmail, to enable getting a member via a site alias and an email.

Now, after modeling the data, let’s see how we can access it:

Get a Membership Item

Create or update a Membership Item

Another helpful usage of DynamoDBMapper is with expressions. Let’s explore it with an example:

When trying to delete an item with DynamoDBMapper, the behaviour is that the item will be deleted if it exists and nothing will happen otherwise. Sometimes, you might want an error to occur when trying to delete an item that doesn’t exist, as it is an illegal state. You may think that in order to include this validation you’ll need to make two requests to the database — one to check if the item exists and the second to delete the item.
But using DynamoDBMapper enables us to do that in just one request to the database. You can declare a DynamoDBDeleteExpression with a condition, and pass it when deleting the item. In case the condition is not met, a ConditionalCheckFailedException will be raised.

Delete a Member Item using DynamoDBMapper with conditional expression

Expressions can also be used when fetching items by LSI. For example, getting members via site alias and email:

Get Members by LSI using query expression

Wrapping up

Applying the single table design pattern gets you the best of both worlds — scalability and great query capabilities for data stored in DynamoDB. DynamoDB has allowed Duda to support membership and scale to millions of members, while maintaining our fast performance time.

If you have any questions or comments, feel free to message me at https://www.linkedin.com/in/sarit-pinhas/.