What I learned From Merging Two DynamodDB Tables

We had OrderRecord and TrackingRecord in separate tables. Here's what happened when we put them together, and what I wish I'd known beforehand.

Our e-commerce system had two tables that were constantly talking to each other:

// OrderRecord table
{
  PK: "order_12345",
  customerId: "customer_456",
  totalAmount: 99.99,
  orderStatus: "confirmed"
}

// TrackingRecord table  
{
  PK: "tracking_67890",
  orderId: "order_12345", // Always needed to link back
  carrier: "UPS",
  trackingNumber: "1Z999AA1234567890",
  deliveryStatus: "in_transit"
}

Every time we showed order details to customers, we made two API calls. It worked, but it felt wasteful.

I realized we were fighting DynamoDB instead of working with it. The database was designed for single-table pattern, and here we were trying to make it behave like a relational database.

The breakthrough came when I stopped thinking about "tables" and started thinking about "access patterns":

• Get order with tracking info
• Find order by tracking number
• Update order status

Once I mapped these out, the single-table approach made perfect sense.

Instead of two separate tables, everything went into one:

// Order data
{
  PK: "ORDER#12345",
  SK: "METADATA", 
  customerId: "customer_456",
  totalAmount: 99.99,
  orderStatus: "confirmed"
}

// Tracking data (same partition key!)
{
  PK: "ORDER#12345",
  SK: "TRACKING#67890",
  carrier: "UPS", 
  trackingNumber: "1Z999AA1234567890",
  deliveryStatus: "in_transit"
}

Now I could get both records with a single query:

public List<Map<String, AttributeValue>> getCompleteOrder(String orderId) {
    QueryRequest request = QueryRequest.builder()
        .tableName("OrderTable")
        .keyConditionExpression("PK = :pk")
        .expressionAttributeValues(Map.of(
            ":pk", AttributeValue.builder().s("ORDER#" + orderId).build()
        ))
        .build();
        
    return dynamoDb.query(request).items();
}

The biggest challenge was handling "find order by tracking number" requests. Customers would call with just a tracking number, expecting us to find their order.

I tried two approaches:

// Main tracking record
{
  PK: "ORDER#12345",
  SK: "TRACKING#67890", 
  trackingNumber: "1Z999AA1234567890",
  // ... other fields
}

// Lookup record
{
  PK: "TRACK#1Z999AA1234567890",
  SK: "METADATA",
  orderId: "ORDER#12345",
  // ... duplicate essential fields
}

The code actually got simpler. No more complex logic to stitch together data from different tables. Everything came back in one response.

Migration was scarier in theory than practice. I was worried about data consistency, but the dual-write approach (write to both old and new tables during transition) made it pretty smooth.

DynamoDB pricing favors fewer, larger requests over many small ones. I was hesitant about the duplicate data approach because of "storage costs," but the read savings more than made up for it.

Composite keys aren't as scary as they look. PK: "ORDER#12345", SK: "TRACKING#67890" felt weird at first, but it's just a different way of organizing data.

BatchGetItem is your friend. When I did need to fetch multiple related items efficiently, BatchGetItem was much better than multiple individual calls.

I spent too much time overthinking the design. The single-table pattern has well-established conventions - I should have just followed them instead of trying to reinvent anything.

I also should have measured the actual performance impact earlier. The improvements were so clear that it would have justified the migration effort much sooner.

Moving from two tables to one wasn't just about following DynamoDB best practices - it genuinely made our application easy to extend. The key insight was stopping the fight against DynamoDB's design and embracing what it's good at.

If you're making multiple API calls to get related data from DynamoDB, you're probably doing more work than you need to. Single-table design isn't always the answer, but for related entities that are frequently accessed together, it's usually the right choice.

The biggest lesson? Sometimes the "weird" NoSQL way of doing things is weird for a good reason.