Single Table Design in AWS DynamoDB

As a developer working with AWS DynamoDB, I recently learnt and implemented Single Table Design in my projects. Single Table Design is a powerful and efficient approach to data modeling that has transformed the way I structure and query data in DynamoDB.

In this article, I want to share my learning experience, demonstrate the concepts I've grasped, and showcase the benefits of Single Table Design through real-world examples.

Implementing Single Table Design

Let's explore how to implement Single Table Design using a real-world example of a blog application. We'll consider scenarios such as creating users, articles, comments, and likes.

Scenario 1: Creating a User
When creating a user, we store the user information in the table with the following structure:

{
  "pk": "USER#789",
  "sk": "PROFILE",
  "user_name": "John Doe",
  "email": "[e-mail]",
  "created_at": "2023-05-20"
}

• The PK is set to USER#, uniquely identifying the user.
• The SK is set to a constant value, such as PROFILE, to indicate that this item represents the user's profile information.

Scenario 2: Creating an Article
When a user creates an article, we store the article information in the table with the following structure:

{
  "pk": "USER#789",
  "sk": "ARTICLE#123",
  "title": "Introduction to Single Table Design",
  "content": "Single Table Design is a powerful technique in DynamoDB...",
  "created_at": "2023-05-20"
}

• The PK is set to USER#, indicating the user who created the article.
• The SK is set to ARTICLE#, uniquely identifying the article within the user's partition.

To query all articles created by a specific user, we can use the following query:

string userId = "789";
var request = new QueryRequest
{
    TableName = "BlogApplication",
    KeyConditionExpression = "pk = :user_id AND begins_with(sk, :article_prefix)",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        {":user_id", new AttributeValue {S = $"USER#{userId}"}},
        {":article_prefix", new AttributeValue {S = "ARTICLE#"}}
    }
};

var response = await dynamoDbClient.QueryAsync(request);
var articles = response.Items;

Scenario 3: User Commenting on an Article
When a user comments on an article, we store the comment information in the table with the following structure:

{
  "pk": "USER#789",
  "sk": "INTERACTION#COMMENT#ARTICLE#123#456",
  "interaction_type": "COMMENT",
  "content": "Great article! Thanks for sharing.",
  "article_id": "ARTICLE#123",
  "created_at": "2023-05-21"
}

• The PK is set to USER#, indicating the user who made the comment.
• The SK is set to INTERACTION#COMMENT#ARTICLE##, uniquely identifying the comment within the user's partition and associating it with the article.

To query all comments made by a specific user, we can use the following query:

string userId = "789";
var request = new QueryRequest
{
    TableName = "BlogApplication",
    KeyConditionExpression = "pk = :user_id AND begins_with(sk, :comment_prefix)",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        {":user_id", new AttributeValue {S = $"USER#{userId}"}},
        {":comment_prefix", new AttributeValue {S = "INTERACTION#COMMENT#"}}
    }
};

var response = await dynamoDbClient.QueryAsync(request);
var comments = response.Items;

Scenario 4: User Liking an Article
When a user likes an article, we store the like information in the table with the following structure:

{
  "pk": "USER#567",
  "sk": "INTERACTION#LIKE#ARTICLE#123",
  "interaction_type": "LIKE",
  "article_id": "ARTICLE#123",
  "created_at": "2023-05-20"
}

• The PK is set to USER#, indicating the user who liked the article.
• The SK is set to INTERACTION#LIKE#ARTICLE#, uniquely identifying the like within the user's partition and associating it with the article.

To query all articles liked by a specific user, we can use the following query:

string userId = "567";
var request = new QueryRequest
{
    TableName = "BlogApplication",
    KeyConditionExpression = "pk = :user_id AND begins_with(sk, :like_prefix)",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        {":user_id", new AttributeValue {S = $"USER#{userId}"}},
        {":like_prefix", new AttributeValue {S = "INTERACTION#LIKE#"}}
    }
};

var response = await dynamoDbClient.QueryAsync(request);
var likedArticles = response.Items;

Simplified the Query Pattern through Mermaid visualization :

Cost Optimization

Single Table Design allows for cost optimization in DynamoDB by minimizing the need for expensive table scans and reducing the amount of data stored.

Efficient Querying:

By designing your PK and SK to support your querying patterns, you can retrieve related items using a single query. This eliminates the need for costly table scans and reduces the amount of throughput consumed.

Reduced Storage Costs:

Storing related entities in a single table can help reduce storage costs compared to storing them in separate tables. DynamoDB charges based on the amount of data stored, so minimizing data duplication across tables can lead to cost savings.

Throughput Optimization: With Single Table Design, you can leverage the PK and SK to distribute data evenly across partitions. This allows for efficient use of provisioned throughput and helps avoid hot partitions, which can lead to throttling and increased costs.

Conclusion :

Single Table Design is a powerful approach to data modeling in AWS DynamoDB. By storing related entities in a single table and leveraging the PK and SK for efficient querying, you can simplify your data management, improve performance, and optimize costs.

When implementing Single Table Design, consider your application's access patterns and design your PK and SK accordingly. Use a combination of PK and SK to uniquely identify items and support efficient querying based on your requirements.

By following the examples and scenarios discussed in this article, you can effectively implement Single Table Design in your DynamoDB applications and reap the benefits of simplified data management, efficient querying, and cost optimization.

Remember to carefully analyze your data access patterns and optimize your PK and SK design to ensure the best performance and cost-effectiveness for your specific use case.

Happy coding with DynamoDB and Single Table Design!

Comments

[Light]

Well, how to handle this scenario since many users can like or comment on an article, imagine if the article author updates the article title, we have to update the title for all comment/likes for all users who have made it on that title isn't that would be kind of inefficient?

[manojlingala]

I appreciate your comment.

The article already addresses your concern by using the article_id attribute to reference the associated article instead of storing the full article title.

To handle the scenario of updating the article title efficiently, the approach described in the article is sufficient. When the article title is updated, it only needs to be modified in the article item itself. The comments and likes referencing that article through the article_id attribute will automatically reflect the updated title when queried.