Streamlining NoSQL Database Design with AI: A Case Study using Amazon DynamoDB

In this article, we explore a basic yet practical use case, and demonstrate how it can be modeled in Amazon DynamoDB with a single-table design approach. Leveraging NoSQL Architect, an AI-powered tool, we showcase the potential of automated database design.

E-commerce Application Example

Let's consider an e-commerce application for processing customer orders. Here's a breakdown of the entities and their attributes:

• Product: (product_id, name, category, price, quantity)
• Order: (order_id, customer_id, order_date, status)
• Customer: (customer_id, name, email, shipping_address)

The application needs to support these queries:

• Query products by category
• Query orders based on customer ID and order status
• Query customer details along with all their associated orders
• Query the latest orders for a specific customer

Automating Design with NoSQL Architect

With information about entities and queries, we can generate a database design using NoSQL Architect.

Here, the Entities/Cardinalities table specifies entities, data fields and their cardinalities. Cardinality is the estimated number of unique entity items associated with a field. For example, in Product entity, p/id field has a cardinality of 1. because p/id is a unique field - it is associated with exactly one Product record. Entity Customer and field p/id has a cardinality of 0 because there's no association. Similarly, Product and p/name has a cardinality of 2 (max), because we estimate that there can be at most 2 products with the same name based on our sample data. It's important to note that cardinality can be modeled for maximum, average, minimum or any other relevant statistic. In our example we use max.

Every case is different, and these assumptions about data will likely not hold true for a different e-commerce system. Moreover, these assumptions may change with time, in which case the design desicions should be revised. This is where automation tools like NoSQL Architect are most useful. By simlpy updating the inputs, NoSQL Architect can automatically output an optimal schema for the database.

Optimized Design in Seconds

NoSQL Architect delivers a cost-effective database design optimized for both read and storage efficiency, typically within seconds. Here's a sample solution:

Read-optimized schema
| :table-cnt | :table |        :pk |       :sk |  :entity |
|------------+--------+------------+-----------+----------|
|    1101000 |   MAIN |       p/id |           |  Product |
|    1101000 |   MAIN |       o/id |           |    Order |
|    1101000 |   MAIN |       c/id |           | Customer |
|    1001000 |   GSI1 |       c/id |  o/status |    Order |
|    1001000 |   GSI1 |       c/id | c/address | Customer |
|     100000 |   GSI2 | p/category |           |  Product |


Queries
|              :query | :query-tbl | :query-cost |
|---------------------+------------+-------------|
|     Order by status |       GSI1 |           5 |
| All customer orders |       GSI1 |         100 |
|       Latest orders |       GSI1 |         100 |
|   Prod. by category |       GSI2 |       10000 |

Summary

NoSQL Architect offers a unique, free solution for generating optimized database schemas, setting a new standard in database design automation. This AI-powered tool allows to create efficient single-table designs that scale effortlessly with your application's growth. As software requirements evolve, the database schema must accommodate these changes. Redesigning is an extremely costly endeavor, and reducing development costs is the primary motivation behind NoSQL Architect.