# Example of modeling relational data in DynamoDB
This example describes how to model relational data in Amazon DynamoDB. The DynamoDB table design corresponds to the relational order entry schema that is shown in [Relational modeling](bp-relational-modeling.md). This design uses multiple specialized tables rather than a single adjacency list, providing clear operational boundaries while leveraging strategic GSIs to serve all access patterns efficiently.
The design approach uses aggregate-oriented principles, grouping data based on access patterns rather than rigid entity boundaries. Key design decisions include using separate tables for entities with low access correlation, embedding related data when always accessed together, and using item collections for identifying relationships.
The following tables and their accompanying indexes support the relational order entry schema:
## Employee Table Design
The Employee table stores employee information as a single entity per item, optimized for direct employee lookups and supporting multiple query patterns through strategic GSIs. This table demonstrates the principle of designing separate tables for entities with independent operational characteristics and low cross-entity access correlation.
The table uses a simple partition key (employee\$1id) without a sort key, as each employee is a distinct entity. Four GSIs enable efficient querying by different attributes:
+ *EmployeeByName GSI* - Uses INCLUDE projection with all employee attributes to support complete employee detail retrieval by name, handling potential duplicate names with employee\$1id as sort key
+ *EmployeeByWarehouse GSI* - Uses INCLUDE projection with only essential attributes (name, job\$1title, hire\$1date) to minimize storage costs while supporting warehouse-based queries
+ *EmployeeByJobTitle GSI* - Enables role-based queries with INCLUDE projection for reporting and organizational analysis
+ *EmployeeByHireDate GSI* - Uses a static partition key value "EMPLOYEE" with hire\$1date as sort key to enable efficient date range queries for recent hires. Since employee additions/updates are typically under 1,000 WCU, a single partition can handle the write load without hot partition issues
**Employee Table - Base Table Structure**
| employee\$1id (PK) | name | phone\$1numbers | warehouse\$1id | job\$1title | hire\$1date | entity\$1type |
| --- | --- | --- | --- | --- | --- | --- |
| emp\$1001 | John Smith | ["\$11-555-0101"] | wh\$1sea | Manager | 2024-03-15 | EMPLOYEE |
| emp\$1002 | Jane Doe | ["\$11-555-0102", "\$11-555-0103"] | wh\$1sea | Associate | 2025-01-10 | EMPLOYEE |
| emp\$1003 | Bob Wilson | ["\$11-555-0104"] | wh\$1pdx | Associate | 2025-06-20 | EMPLOYEE |
| emp\$1004 | Alice Brown | ["\$11-555-0105"] | wh\$1pdx | Supervisor | 2023-11-05 | EMPLOYEE |
| emp\$1005 | Charlie Davis | ["\$11-555-0106"] | wh\$1sea | Associate | 2025-12-01 | EMPLOYEE |
**EmployeeByName GSI - Supporting Employee Name Queries**
| name (GSI-PK) | employee\$1id (GSI-SK) | phone\$1numbers | warehouse\$1id | job\$1title | hire\$1date |
| --- | --- | --- | --- | --- | --- |
| Alice Brown | emp\$1004 | ["\$11-555-0105"] | wh\$1pdx | Supervisor | 2023-11-05 |
| Bob Wilson | emp\$1003 | ["\$11-555-0104"] | wh\$1pdx | Associate | 2025-06-20 |
| Charlie Davis | emp\$1005 | ["\$11-555-0106"] | wh\$1sea | Associate | 2025-12-01 |
| Jane Doe | emp\$1002 | ["\$11-555-0102", "\$11-555-0103"] | wh\$1sea | Associate | 2025-01-10 |
| John Smith | emp\$1001 | ["\$11-555-0101"] | wh\$1sea | Manager | 2024-03-15 |
**EmployeeByWarehouse GSI - Supporting Warehouse Queries**
| warehouse\$1id (GSI-PK) | employee\$1id (GSI-SK) | name | job\$1title | hire\$1date |
| --- | --- | --- | --- | --- |
| wh\$1pdx | emp\$1003 | Bob Wilson | Associate | 2025-06-20 |
| wh\$1pdx | emp\$1004 | Alice Brown | Supervisor | 2023-11-05 |
| wh\$1sea | emp\$1001 | John Smith | Manager | 2024-03-15 |
| wh\$1sea | emp\$1002 | Jane Doe | Associate | 2025-01-10 |
| wh\$1sea | emp\$1005 | Charlie Davis | Associate | 2025-12-01 |
**EmployeeByJobTitle GSI - Supporting Job Title Queries**
| job\$1title (GSI-PK) | employee\$1id (GSI-SK) | name | warehouse\$1id | hire\$1date |
| --- | --- | --- | --- | --- |
| Associate | emp\$1002 | Jane Doe | wh\$1sea | 2025-01-10 |
| Associate | emp\$1003 | Bob Wilson | wh\$1pdx | 2025-06-20 |
| Associate | emp\$1005 | Charlie Davis | wh\$1sea | 2025-12-01 |
| Manager | emp\$1001 | John Smith | wh\$1sea | 2024-03-15 |
| Supervisor | emp\$1004 | Alice Brown | wh\$1pdx | 2023-11-05 |
**EmployeeByHireDate GSI - Supporting Recent Hire Queries**
| entity\$1type (GSI-PK) | hire\$1date (GSI-SK) | employee\$1id | name | warehouse\$1id |
| --- | --- | --- | --- | --- |
| EMPLOYEE | 2023-11-05 | emp\$1004 | Alice Brown | wh\$1pdx |
| EMPLOYEE | 2024-03-15 | emp\$1001 | John Smith | wh\$1sea |
| EMPLOYEE | 2025-01-10 | emp\$1002 | Jane Doe | wh\$1sea |
| EMPLOYEE | 2025-06-20 | emp\$1003 | Bob Wilson | wh\$1pdx |
| EMPLOYEE | 2025-12-01 | emp\$1005 | Charlie Davis | wh\$1sea |
## Customer Table Design
The Customer table maintains customer information with strategic denormalization of account\$1rep\$1id to enable efficient account representative queries. This design choice trades slight storage overhead for query performance, eliminating the need for joins between customer and account representative data.
The table supports multiple phone numbers per customer using a list attribute, demonstrating DynamoDB's schema flexibility. The single GSI enables account representative workflows:
+ *CustomerByAccountRep GSI* - Uses INCLUDE projection with name and email attributes to support account rep customer management without requiring full customer record retrieval
**Customer Table - Base Table Structure**
| customer\$1id (PK) | name | phone\$1numbers | email | account\$1rep\$1id |
| --- | --- | --- | --- | --- |
| cust\$1001 | Acme Corp | ["\$11-555-1001"] | contact@acme.com | rep\$1001 |
| cust\$1002 | TechStart Inc | ["\$11-555-1002", "\$11-555-1003"] | info@techstart.com | rep\$1001 |
| cust\$1003 | Global Traders | ["\$11-555-1004"] | sales@globaltraders.com | rep\$1002 |
| cust\$1004 | BuildRight LLC | ["\$11-555-1005"] | orders@buildright.com | rep\$1002 |
| cust\$1005 | FastShip Co | ["\$11-555-1006"] | support@fastship.com | rep\$1003 |
**CustomerByAccountRep GSI - Supporting Account Rep Queries**
| account\$1rep\$1id (GSI-PK) | customer\$1id (GSI-SK) | name | email |
| --- | --- | --- | --- |
| rep\$1001 | cust\$1001 | Acme Corp | contact@acme.com |
| rep\$1001 | cust\$1002 | TechStart Inc | info@techstart.com |
| rep\$1002 | cust\$1003 | Global Traders | sales@globaltraders.com |
| rep\$1002 | cust\$1004 | BuildRight LLC | orders@buildright.com |
| rep\$1003 | cust\$1005 | FastShip Co | support@fastship.com |
## Order Table Design
The Order table uses vertical partitioning with separate items for order headers and order items. This design enables efficient product-based queries while maintaining all order components within the same partition for efficient access. Each order consists of multiple items:
+ *Order Header* - Contains order metadata with PK=order\$1id, SK=order\$1id
+ *Order Items* - Individual line items with PK=order\$1id, SK=product\$1id, enabling direct product queries
**Note**
This vertical partitioning approach trades the simplicity of embedded order items for enhanced query flexibility. Each order item becomes a separate DynamoDB item, enabling efficient product-based queries while maintaining all order data within the same partition for efficient retrieval in a single request.
The table includes strategic denormalization of account\$1rep\$1id (duplicated from Customer table) to enable direct account representative queries without requiring customer lookups. For high-throughput write scenarios, OPEN orders include status and shard attributes to enable write sharding across multiple partitions.
Four GSIs support different query patterns with optimized projections:
+ *OrderByCustomerDate GSI* - Uses INCLUDE projection with order summary and item details to support customer order history with date range filtering
+ *OpenOrdersByDate GSI (Sparse, Sharded)* - Uses multi-attribute partition key (status \$1 shard) with 5 shards to distribute 5,000 WPS (writes per second) across partitions (1,000 WPS each, matching DynamoDB's 1,000 WCU per partition limit). Only indexes OPEN orders (20% of total), which can help reduce GSI storage costs. Requires parallel queries across all 5 shards with client-side result merging
+ *OrderByAccountRep GSI* - Uses INCLUDE projection with order summary attributes to support account representative workflows without full order details
+ *ProductInOrders GSI* - Created from OrderItem records (PK=order\$1id, SK=product\$1id), this GSI enables queries to find all orders containing a specific product. Uses INCLUDE projection with order context (customer\$1id, order\$1date, quantity) for product demand analysis
**Order Table - Base Table Structure (Vertical Partitioning)**
| PK | SK | customer\$1id | order\$1date | status | account\$1rep\$1id | quantity | price | shard |
| --- | --- | --- | --- | --- | --- | --- | --- | --- |
| ord\$1001 | ord\$1001 | cust\$1001 | 2025-11-15 | CLOSED | rep\$1001 | | | |
| ord\$1001 | prod\$1100 | | | | | 5 | 25.00 | |
| ord\$1002 | ord\$1002 | cust\$1001 | 2025-12-20 | OPEN | rep\$1001 | | | 0 |
| ord\$1002 | prod\$1101 | | | | | 10 | 15.00 | |
| ord\$1003 | ord\$1003 | cust\$1002 | 2026-01-05 | OPEN | rep\$1001 | | | 2 |
| ord\$1003 | prod\$1100 | | | | | 3 | 25.00 | |
**OrderByCustomerDate GSI - Supporting Customer Order Queries**
| customer\$1id (GSI-PK) | order\$1date (GSI-SK) | order\$1id | status | total\$1amount | order\$1items | shard |
| --- | --- | --- | --- | --- | --- | --- |
| cust\$1001 | 2025-11-15 | ord\$1001 | CLOSED | 225.00 | [\$1product\$1id: "prod\$1100", qty: 5\$1] | |
| cust\$1001 | 2025-12-20 | ord\$1002 | OPEN | 150.00 | [\$1product\$1id: "prod\$1101", qty: 10\$1] | 0 |
| cust\$1002 | 2026-01-05 | ord\$1003 | OPEN | 175.00 | [\$1product\$1id: "prod\$1100", qty: 3\$1] | 2 |
| cust\$1003 | 2025-10-10 | ord\$1004 | CLOSED | 250.00 | [\$1product\$1id: "prod\$1101", qty: 5\$1] | |
| cust\$1004 | 2026-01-03 | ord\$1005 | OPEN | 200.00 | [\$1product\$1id: "prod\$1100", qty: 20\$1] | 1 |
**OpenOrdersByDate GSI (Sparse, Sharded) - Supporting High-Throughput Open Order Queries**
| status (GSI-PK-1) | shard (GSI-PK-2) | order\$1date (SK) | order\$1id | customer\$1id | account\$1rep\$1id | order\$1items | total\$1amount |
| --- | --- | --- | --- | --- | --- | --- | --- |
| OPEN | 0 | 2025-12-20 | ord\$1002 | cust\$1001 | rep\$1001 | [\$1product\$1id: "prod\$1101", qty: 10\$1] | 150.00 |
| OPEN | 1 | 2026-01-03 | ord\$1005 | cust\$1004 | rep\$1002 | [\$1product\$1id: "prod\$1100", qty: 20\$1] | 200.00 |
| OPEN | 2 | 2026-01-05 | ord\$1003 | cust\$1002 | rep\$1001 | [\$1product\$1id: "prod\$1100", qty: 3\$1] | 175.00 |
**OrderByAccountRep GSI - Supporting Account Rep Order Queries**
| account\$1rep\$1id (GSI-PK) | order\$1date (GSI-SK) | order\$1id | customer\$1id | status | total\$1amount |
| --- | --- | --- | --- | --- | --- |
| rep\$1001 | 2025-11-15 | ord\$1001 | cust\$1001 | CLOSED | 225.00 |
| rep\$1001 | 2025-12-20 | ord\$1002 | cust\$1001 | OPEN | 150.00 |
| rep\$1001 | 2026-01-05 | ord\$1003 | cust\$1002 | OPEN | 175.00 |
| rep\$1002 | 2025-10-10 | ord\$1004 | cust\$1003 | CLOSED | 250.00 |
| rep\$1002 | 2026-01-03 | ord\$1005 | cust\$1004 | OPEN | 200.00 |
**ProductInOrders GSI - Supporting Product Order Queries**
| product\$1id (GSI-PK) | order\$1id (GSI-SK) | customer\$1id | order\$1date | quantity |
| --- | --- | --- | --- | --- |
| prod\$1100 | ord\$1001 | cust\$1001 | 2025-11-15 | 5 |
| prod\$1100 | ord\$1003 | cust\$1002 | 2026-01-05 | 3 |
| prod\$1101 | ord\$1002 | cust\$1001 | 2025-12-20 | 10 |
## Product Table Design
The Product table uses the item collection pattern to store both product metadata and inventory data within the same partition. This design leverages the identifying relationship between products and inventory - inventory cannot exist without a parent product. Using PK=product\$1id with SK=product\$1id for product metadata and SK=warehouse\$1id for inventory items eliminates the need for a separate Inventory table and GSI, reducing costs by approximately 50%.
This pattern enables efficient queries for both individual warehouse inventory (GetItem with composite key) and all warehouse inventory for a product (Query on partition key). The total\$1inventory attribute in the product metadata item provides denormalized aggregation for quick total inventory lookups.
**Product Table - Base Table Structure (Item Collection Pattern)**
| product\$1id (PK) | warehouse\$1id (SK) | product\$1name | category | unit\$1price | inventory\$1quantity | total\$1inventory |
| --- | --- | --- | --- | --- | --- | --- |
| prod\$1100 | prod\$1100 | Widget A | Hardware | 25.00 | | 500 |
| prod\$1100 | wh\$1sea | | | | 200 | |
| prod\$1100 | wh\$1pdx | | | | 150 | |
| prod\$1100 | wh\$1atl | | | | 150 | |
| prod\$1101 | prod\$1101 | Gadget B | Electronics | 50.00 | | 300 |
| prod\$1101 | wh\$1sea | | | | 100 | |
| prod\$1101 | wh\$1pdx | | | | 200 | |
Each table is designed with specific Global Secondary Indexes (GSIs) to support the required access patterns efficiently. The design uses aggregate-oriented principles with strategic denormalization and sparse indexing to optimize both performance and cost.
Key design optimizations include:
+ *Sparse GSI* - OpenOrdersByDate only indexes OPEN orders (20% of total), which can help reduce GSI storage costs
+ *Item Collection Pattern* - Product table stores inventory using PK=product\$1id, SK=warehouse\$1id to eliminate separate inventory table
+ *Order \$1 OrderItems Aggregation* - Embedded as single item due to 100% access correlation
+ *Strategic Denormalization* - account\$1rep\$1id duplicated in Order table for efficient queries
Finally, you can revisit the access patterns that were defined earlier. The following table shows how each access pattern is efficiently supported using the multi-table design with strategic GSIs. Each pattern uses either direct key lookups or single GSI queries, avoiding expensive scans and providing consistent performance at any scale.
| S. No. | Access patterns | Query conditions |
| --- | --- | --- |
| 1 | Look up Employee Details by Employee ID | Employee Table: GetItem(employee\$1id="emp\$1001") |
| 2 | Query Employee Details by Employee Name | EmployeeByName GSI: Query(name="John Smith") |
| 3 | Find an Employee's Phone Number(s) | Employee Table: GetItem(employee\$1id="emp\$1001") |
| 4 | Find a Customer's Phone Number(s) | Customer Table: GetItem(customer\$1id="cust\$1001") |
| 5 | Get Orders for Customer within Date Range | OrderByCustomerDate GSI: Query(customer\$1id="cust\$1001", order\$1date BETWEEN "2025-01-01" AND "2025-12-31") |
| 6 | Show all Open Orders within Date Range | OpenOrdersByDate GSI: Query 5 shards in parallel with multi-attribute PK (status="OPEN" \$1 shard=0-4), SK=order\$1date BETWEEN "2025-01-01" AND "2025-12-31", merge results |
| 7 | See all Employees hired recently | EmployeeByHireDate GSI: Query(entity\$1type="EMPLOYEE", hire\$1date >= "2025-01-01") |
| 8 | Find all Employees in Warehouse | EmployeeByWarehouse GSI: Query(warehouse\$1id="wh\$1sea") |
| 9 | Get all Items on Order for Product | ProductInOrders GSI: Query(product\$1id="prod\$1100") |
| 10 | Get Inventories for Product at all Warehouses | Product Table: Query(product\$1id="prod\$1100") |
| 11 | Get Customers by Account Rep | CustomerByAccountRep GSI: Query(account\$1rep\$1id="rep\$1001") |
| 12 | Get Orders by Account Rep | OrderByAccountRep GSI: Query(account\$1rep\$1id="rep\$1001") |
| 13 | Get Employees with Job Title | EmployeeByJobTitle GSI: Query(job\$1title="Manager") |
| 14 | Get Inventory by Product and Warehouse | Product Table: GetItem(product\$1id="prod\$1100", warehouse\$1id="wh\$1sea") |
| 15 | Get Total Product Inventory | Product Table: GetItem(product\$1id="prod\$1100", warehouse\$1id="prod\$1100") |