DynamoDB Data Modeling: An Effective way to start...

Effective database design is crucial for the performance and scalability of our applications but also to perform re-architect tasks for App Modernization Journey.

DynamoDB, AWS's fully managed NoSQL database service, offers great flexibility in data modeling. In this article, i will explore the fundamental and some advanced concepts of data modeling in DynamoDB, applying them to a practical context: Business Rules for Retails Stores.

Embarking on the journey of DynamoDB data modeling can be both exciting and challenging. i'll try to demystify the intricacies of data modeling in DynamoDB. Whether you're a beginner or an intermediate user, join us as we explore key concepts, practical exercises and examples.

Tenets of NoSQL Data Modeling

The tenets of NoSQL data modeling focus on leveraging the features and flexibilities offered by NoSQL databases. Here are some mentioned:

Dynamic Schema: NoSQL databases allow dynamic schemas, meaning each record can have different attributes without requiring a fixed structure. This provides flexibility to adapt to changes in data without modifying the database schema.

Denormalization: Unlike relational databases, where normalization is favored to reduce redundancy, NoSQL commonly embraces denormalization. Incorporating redundant data in a single document or record facilitates more efficient queries and reduces the need for multiple queries to obtain complete information.

Query-Centric Modeling: Data design in NoSQL often relies on the most common query patterns. Instead of designing the data structure for all possible operations, priority is given to queries that are performed most frequently, thus optimizing performance for specific use cases.

Eventual Consistency: Many NoSQL databases adopt the eventual consistency model instead of immediate consistency. This implies that after a write, eventual consistency will be achieved, but not immediately. This approach favors availability and partition tolerance in distributed systems.

Partitioning: Partitioning distributes data among nodes or servers to improve efficiency and scalability. Designing data models with partitioning in mind helps distribute the load evenly and minimizes bottlenecks.

Strategic Index Usage: Instead of relying solely on secondary indexes, NoSQL databases often favor the creation of strategic indexes aligned with the most frequent queries. These indexes can be composite and customized to optimize performance.

How we can start?

Define the use case:
This is the first step and involves identifying and clarifying the purpose and objectives of the system or application you are designing. This sets the foundation for making informed decisions during the data modeling design.

Identify access patterns:
Examine how data will be accessed and queried in your application. Identifying these patterns helps design a data model that optimizes query efficiency and operations.

Read/Write workloads:
Understand the relative proportions of read and write operations in your system. This influences the design of your database to optimize performance based on the most common operations.

Query dimensions:
Identify the different ways in which data will be queried and aim to optimize the data model to efficiently support these queries.

Aggregations:
Consider how data should be aggregated and summarized to support efficient aggregation operations such as sums, averages, or item counting.

Design the data model that best suits your needs. This may involve creating tables, defining primary and secondary keys, and establishing relationships but also following simple best practices:

Avoid relational design patterns:
In NoSQL databases like DynamoDB, it is often beneficial to avoid traditional relational database design patterns. This includes extreme normalization.

Start with one table, but use as many as required:
Begin with a simple design using one table, but do not hesitate to create additional tables as needed for your application. DynamoDB favors a polyglot approach to adapt to different use cases.

Understanding Tables in DynamoDB

DynamoDB's table structure, diverging from traditional relational databases, revolves around primary keys, sort keys, and attributes.

Primary Keys: The primary key is the cornerstone of a DynamoDB table, serving as the identifier for each item within it.

Components:

• Partition Key: Often referred to as the hash key, it determines the partition or storage location of the item based on its value.
• Sort Key: Also known as the range key, it comes into play when items share the same partition key, facilitating efficient sorting and querying within that partition.

Identifying Primary Keys:
Primary keys in DynamoDB are fundamental to table design. They are divided into two components: the partition key and the sort key. The partition key determines the partition in which the item will be stored, while the sort key organizes items within the partition. Properly identifying primary keys is crucial for query performance and efficiency.

How are Inserts and Reads?
In DynamoDB, the performance of write and read operations is directly related to the table design and primary keys. DynamoDB is highly scalable and distributed, but optimal performance is achieved by evenly distributing operations across partitions and keys. Inserts and reads benefit from designing primary keys that effectively distribute the load.

Avoid Overloading Items into Partitions:
DynamoDB distributes data across partitions, and the service's efficiency is optimized when items are evenly distributed among partitions. Overloading items in a single partition can create bottlenecks and impact performance. It is essential to design primary keys to evenly distribute the load and avoid overloading a single partition. This is known as a "hot partition" and should be avoided to ensure balanced performance.

Sort Keys:

• Functionality: While the partition key is fundamental for item retrieval, the sort key adds an extra layer of sophistication by allowing items with the same partition key to be distinguished and organized.
• Use Cases: Sorting items based on attributes such as timestamps or numerical values becomes seamless with the sort key, enabling targeted queries within specific partitions.

Attributes:

• Nature: DynamoDB items are essentially collections of attributes, each of which holds a specific piece of data.
• Flexibility: Attributes accommodate various data types, from simple strings and numbers to complex structures like lists or maps, offering flexibility in data representation.

Key Distinctions from Relational Databases:

• Schema-less Nature: DynamoDB operates on a schema-less or schema-flexible model, allowing each item in a table to have different attributes. This contrasts sharply with the rigid structure of relational databases.
• Indexing Approach: Traditional relational databases heavily rely on indexes for efficient querying. In DynamoDB, the primary key itself acts as a natural index, streamlining access patterns without the need for additional indexing mechanisms.
• Scalability: DynamoDB's architecture, particularly its partitioning mechanism, enhances scalability. The partition key distributes data across multiple nodes, enabling the system to handle varying workloads seamlessly.

Data Access: Read and Write Patterns
Efficient data access is paramount. Learn common read and write patterns to optimize latency and speed. Uncover strategies that DynamoDB offers to handle various workloads effectively.

• Building Queries: In DynamoDB, queries are constructed using the Query operation. You can perform queries using the partition key and, optionally, the sort key to retrieve a specific set of items. Queries are efficient in DynamoDB and benefit from a well-structured primary key design.
• Sort Key Condition vs. Filter Expressions: When conducting queries in DynamoDB, you can specify conditions on both the partition key and the sort key using KeyConditionExpression. Additionally, you can apply filter expressions using FilterExpression. The main difference lies in that KeyConditionExpression operates on the keys directly and is more efficient, while FilterExpression filters the results after they have been retrieved.
• Composite Keys:Composite keys, or composite primary keys, are an essential feature in DynamoDB. They enable the creation of more complex data models by combining a partition key and a sort key. This allows for efficient queries and logical organization of data. A well-designed composite key is crucial to fully leverage DynamoDB's query capabilities.

Now, go with some exercises...

Exercise #1 – Discount Control in Stores

Suppose you are designing a database to control the rules regarding discounts that a retail store in a specific area of Monterrey, Mexico, can apply at a point of sale (POS) in a single day.

Requirements:

• Each store must have a unique identifier.
• It is necessary to establish a maximum limit for discounts that a store can apply in a day.
• Record when a store has exceeded the daily discount limit.
• Information about the store's location and the specific area within a specific city is required.

Questions to Consider:

Ideal Primary and Secondary Keys:

• What would be the ideal primary and secondary keys for this scenario?
• How can you ensure a unique identifier for each store while efficiently organizing data for queries?

Modeling Discount Rules:

• How would you model the information regarding discount rules in the database?
• Should you create separate tables for store details and discount rules, or can they be effectively managed within a single table?

Attributes Required to Meet Requirements:

• What attributes are necessary to fulfill the specified requirements?
• Consider the data points needed for tracking daily discounts, location information, and identifying when a store surpasses the discount limit.

Possible Solution to Exercise #1 - Primary Keys:

Partition Key:

• Unique Store Identifier.
• Sort Key: Can be the date of the day to control daily limits.

Attributes:

• Maximum Daily Discounts Limit for the Store.
• Discounts Applied in a Day.
• Location Information: Specific Zone within a Specific City.

We can test the functionality of the table by creating a Lambda function with code similar to the following

import boto3

def query_discount_rules_for_store(store_id, date):
    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('RetailStoreRules')

    filter_expression = Key('StoreID').eq(store_id) & Key('Date').eq(date)

    response = table.query(
        KeyConditionExpression=filter_expression
    )

    items = response.get('Items', [])

    return items

In this example:

• We use the boto3 library.
• The dynamodb.Table method is used to load the DynamoDB table named 'RetailStoreRules'.
• The KeyConditionExpression is constructed using the eq method for equality comparisons.
• The table.query method is called with the constructed filter expression, and the results are retrieved from the response.
• Make sure to install the boto3 library before running the Python code

Now, go with another exercise but using Composite Keys...

Exercise #2 – Composite sort key for discount limits in stores

Let's say you are designing a table called RetailStoreLimits to track the daily discount limits that a retail store in a specific area of Monterrey, Mexico, can apply at a point of sale (POS).

Requirements:

• Each store must have a unique identifier.
• Record the daily discount limits that a store can apply.
• Enable the retrieval of daily discount limits for a specific store within a specified date range.

Possible Solution to Exercise #2 - Composite Keys:

Identify Relevant Attributes:

• StoreID: Unique identifier for the store. -Date: Date for the daily discount limit.
• MaxDailyDiscountLimit: The maximum daily discount limit allowed for the store.

Define the Structure of the Composite Sort Key:

• Combine StoreID and Date to form the composite sort key.
• Composite Sort Key: "StoreID#Date"

Configure the Sort Key in DynamoDB:

• When creating or updating the RetailStoreLimits table in DynamoDB, specify the composite sort key as "StoreID#Date" Ensure that your application also inserts data according to this structure.

Insert Example Data:

• Insert example data into the table to represent daily discount limits for different stores and dates.

{
  "StoreID": "Store001",
  "Date": "2024-01-25",
  "MaxDailyDiscountLimit": 1000
}

Query Daily Discount Limits for a Store and Date Range:

• Use Query with KeyConditionExpression to query the daily discount limits for a specific store within a date range.

from boto3.dynamodb.conditions import Key

def query_discount_limits_for_store(store_id, start_date, end_date):
    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('RetailStoreLimits')

    filter_expression = Key('StoreID').eq(store_id) & Key('Date').between(start_date, end_date)

    response = table.query(
        KeyConditionExpression=filter_expression
    )

    items = response.get('Items', [])

    return items

In this Python version:

• We use the boto3 library.
• The dynamodb.Table method is used to load the DynamoDB table named 'RetailStoreLimits'.
• The KeyConditionExpression is constructed using the eq method for equality comparison and the between method for date range.
• The table.query method is called with the constructed filter expression, and the results are retrieved from the response.

Now let's talk abot LSI and GSI.

In the context of NoSQL databases like DynamoDB, a Local Secondary Index (LSI) is an index associated with a table that shares the same partition key as the main table but has a different sort key. This means that data is organized differently in the index compared to the main table, enabling efficient queries based on the sort key of the LSI.

On the other hand, a Global Secondary Index (GSI) is an independent index of the main table, with its own partition key and, optionally, a different sort key. Unlike LSIs, a GSI does not share the partition key of the main table.

Exercise #3 – Products in Retail store system:

Let's suppose we are designing a NoSQL database for a retail store system. In this scenario, our goal is to manage products and, specifically, perform efficient queries on products based on their category and popularity.

Specific Requirements:

• Record information about products, including their ID, name, category, stock quantity, and the number of times they have been viewed by users.
• Enable the query of products by category and the retrieval of the most popular products overall.

Possible Solution to Exercise #3:

Table Name: RetailProducts
Primary Key: ProductID (Partition Key)
Attributes:
ProductName
Category
StockQuantity
ViewCount

To facilitate efficient queries:

Local Secondary Index (LSI):

• Index Name: CategoryIndex
• Partition Key: Category
• Sort Key:ProductID

Global Secondary Index (GSI):

• Index Name: PopularityIndex
• Partition Key: ViewCount
• Sort Key: ProductID

With this structure, you can efficiently query products by category using the LSI and retrieve the most popular products using the GSI.

We will be sharing an upcoming part to delve further into other concepts and techniques of data modeling for NoSQL. Additionally, we will present simple exercises to put these concepts into practice.