Modeling One-to-Many Relationships in DynamoDB with boto3-assist

In my previous post, we covered the fundamentals of DynamoDB single-table design. Now let's explore one of the most powerful patterns: modeling one-to-many relationships.

This is where single-table design truly shines. By cleverly using partition and sort keys, you can retrieve a parent entity and all its children in a single query—no joins, no multiple round trips.

The One-to-Many Pattern

The key insight is simple but powerful:

For a one-to-many relationship:

• Parent entity: pk = parent_type#parent_id, sk = parent_type#parent_id
• Child entities: pk = parent_type#parent_id, sk = child_type#child_id

Notice that child items share the same partition key as their parent but have different sort keys. This collocates related data in the same partition, enabling efficient single-query retrieval.

Real-World Example: Orders and Order Items

Let's model a common e-commerce scenario where an order can have many order items. Here's how to implement this with boto3-assist:

The Order Model (Parent)

from boto3_assist.dynamodb.dynamodb_model_base import DynamoDBModelBase
from boto3_assist.dynamodb.dynamodb_index import DynamoDBIndex, DynamoDBKey

class Order(DynamoDBModelBase):
    def __init__(self, id=None):
        super().__init__()
        self.id = id
        self.user_id = None
        self.total = 0.0
        self.status = "pending"
        self.__setup_indexes()
    
    def __setup_indexes(self):
        primary = DynamoDBIndex()
        primary.partition_key.attribute_name = "pk"
        primary.partition_key.value = lambda: DynamoDBKey.build_key(
            ("order", self.id)
        )
        primary.sort_key.attribute_name = "sk"
        primary.sort_key.value = lambda: DynamoDBKey.build_key(
            ("order", self.id)
        )
        self.indexes.add_primary(primary)

The OrderItem Model (Child)

class OrderItem(DynamoDBModelBase):
    def __init__(self):
        super().__init__()
        self.id = None
        self.order_id = None  # Parent order's ID
        self.product_id = None
        self.quantity = 0
        self.price = 0.0
        self.__setup_indexes()
    
    def __setup_indexes(self):
        primary = DynamoDBIndex()
        # CRITICAL: Use parent's ID for partition key
        primary.partition_key.attribute_name = "pk"
        primary.partition_key.value = lambda: DynamoDBKey.build_key(
            ("order", self.order_id)
        )
        # Use child's own ID for sort key
        primary.sort_key.attribute_name = "sk"
        primary.sort_key.value = lambda: DynamoDBKey.build_key(
            ("item", self.id)
        )
        self.indexes.add_primary(primary)

What Gets Stored

Here's how the data looks in DynamoDB:

// Order (parent)
{
  "pk": "order#xyz-789",
  "sk": "order#xyz-789",
  "id": "xyz-789",
  "user_id": "user-123",
  "total": 99.99,
  "status": "pending"
}

// Order Item 1 (child)
{
  "pk": "order#xyz-789",
  "sk": "item#item-001",
  "id": "item-001",
  "order_id": "xyz-789",
  "product_id": "prod-456",
  "quantity": 2,
  "price": 29.99
}

// Order Item 2 (child)
{
  "pk": "order#xyz-789",
  "sk": "item#item-002",
  "id": "item-002",
  "order_id": "xyz-789",
  "product_id": "prod-789",
  "quantity": 1,
  "price": 39.99
}

All three items share the same partition key (order#xyz-789), which means they're stored together in DynamoDB and can be retrieved efficiently.

Query Patterns Enabled

This design enables three powerful access patterns:

1. Get Order Only

from boto3_assist.dynamodb import DynamoDB

db = DynamoDB()

# Use get() with both pk and sk
model = Order(id="xyz-789")
response = db.get(model=model, table_name="app-table", do_projections=False)
order = Order().map(response.get("Item"))

This retrieves just the order header.

2. Get Order WITH All Items (The Magic!)

from boto3.dynamodb.conditions import Key

# Query by partition key only (omit sort key)
model = Order(id="xyz-789")
key_condition = model.indexes.primary.key(include_sort_key=False)

response = db.query(
    key=key_condition,
    table_name="app-table"
)

# Parse the results
items = response.get("Items", [])
order = None
order_items = []

for item in items:
    if item.get("sk", "").startswith("order#"):
        order = Order().map(item)
    elif item.get("sk", "").startswith("item#"):
        order_items.append(OrderItem().map(item))

print(f"Order {order.id}: ${order.total}")
print(f"Items: {len(order_items)}")

This is the power of single-table design: One query returns everything!

3. Get Items Only

# Use begins_with on sort key
key_condition = (
    Key("pk").eq("order#xyz-789") & 
    Key("sk").begins_with("item#")
)

response = db.query(key=key_condition, table_name="app-table")
items = [OrderItem().map(item) for item in response.get("Items", [])]

Why This Works

Understanding why this pattern is so effective:

1. Same Partition: The order and its items are stored in the same partition (pk = "order#xyz-789")
2. Sort Key Differentiation: Each item type has a unique sort key pattern
   • Order: sk = "order#xyz-789"
   • Items: sk = "item#item-001", sk = "item#item-002", etc.
3. Flexible Querying: The sort key is optional in queries
   • Omit it → get everything with that partition key
   • Specify it exactly → get one specific item
   • Use begins_with → get items matching a pattern

Service Layer Implementation

In practice, you'd wrap this logic in a service class (we'll cover this more in an upcoming post):

class OrderService:
    def __init__(self, db=None):
        self.db = db or DynamoDB()
        self.table_name = os.environ.get("APP_TABLE_NAME", "app-table")
    
    def get_order_with_items(self, order_id: str) -> dict:
        """Get an order and all its items in a single query."""
        model = Order(id=order_id)
        
        # Query by partition key only
        key_condition = model.indexes.primary.key(include_sort_key=False)
        
        response = self.db.query(
            key=key_condition,
            table_name=self.table_name
        )
        
        items = response.get("Items", [])
        order = None
        order_items = []
        
        for item in items:
            if item.get("sk", "").startswith("order#"):
                order = Order().map(item)
            elif item.get("sk", "").startswith("item#"):
                order_items.append(OrderItem().map(item))
        
        return {
            "order": order,
            "items": order_items
        }

# Usage
service = OrderService()
result = service.get_order_with_items("xyz-789")
print(f"Order total: ${result['order'].total}")
print(f"Item count: {len(result['items'])}")

Another Example: User → Posts

The same pattern works for any one-to-many relationship:

class User(DynamoDBModelBase):
    def __setup_indexes(self):
        primary = DynamoDBIndex()
        primary.partition_key.attribute_name = "pk"
        primary.partition_key.value = lambda: DynamoDBKey.build_key(
            ("user", self.id)
        )
        primary.sort_key.attribute_name = "sk"
        primary.sort_key.value = lambda: DynamoDBKey.build_key(
            ("user", self.id)
        )
        self.indexes.add_primary(primary)

class Post(DynamoDBModelBase):
    def __setup_indexes(self):
        primary = DynamoDBIndex()
        # Share partition key with user
        primary.partition_key.attribute_name = "pk"
        primary.partition_key.value = lambda: DynamoDBKey.build_key(
            ("user", self.user_id)
        )
        # Unique sort key for posts
        primary.sort_key.attribute_name = "sk"
        primary.sort_key.value = lambda: DynamoDBKey.build_key(
            ("post", self.id)
        )
        self.indexes.add_primary(primary)

Query all of Alice's posts:

key = Key('pk').eq('user#alice')
# Returns: User record + all posts in one query

Benefits vs. Traditional Approaches

SQL with JOINs (PostgreSQL, MySQL, etc.)

Relational databases can JOIN tables in a single query:

-- One query with JOIN
SELECT o.*, oi.*
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = 'xyz-789';

This works great in SQL! One query returns everything.

DynamoDB Multi-Table (WITHOUT Single-Table Design)

DynamoDB has NO JOIN capability, so separate tables require multiple queries:

# Query 1: Get order from orders table
order = db.get(table_name="orders", key={"pk": "xyz-789"})

# Query 2: Get items from order_items table
items = db.query(table_name="order_items", key=Key("order_id").eq("xyz-789"))

Two separate database calls = higher latency and cost.

Single-Table Design (DynamoDB with boto3-assist)

Achieves SQL JOIN-like results through keys, not separate queries:

# One query returns order + all items
response = db.query(key=key_condition, table_name="app-table")
# Returns: order + all items (similar to SQL JOIN!)

Why Single-Table Design Wins in DynamoDB:

• ✅ One query instead of two - achieves JOIN-like results through pk/sk design
• ✅ Lower latency - single network call to DynamoDB
• ✅ Lower cost - fewer read capacity units consumed
• ✅ DynamoDB-optimized - leverages DynamoDB's strengths (keys) instead of fighting its limitations (no JOINs)

Best Practices

1. Always Use Lambda for Keys: Ensures keys are evaluated at runtime with current values
2. Consistent Naming: Use descriptive prefixes (order#, item#, user#, post#)
3. Sort Key Patterns: Make sort keys queryable with begins_with
4. Document Relationships: Comment which partition key links parent and child

What's Next?

Now that you understand one-to-many relationships, you're ready to build complete service layers. In my next post, "Defining DynamoDB Models with boto3-assist", I'll show you advanced model patterns including composite sort keys, GSIs, and dynamic key generation.

Get Started

boto3-assist is open source and available on PyPI:

pip install boto3-assist

Check out the complete single-table design documentation in the GitHub repository.

---

About the Author: Eric Wilson is the founder of Geek Cafe and created boto3-assist to make DynamoDB development more intuitive and maintainable.