若数据需独立查询，是否应将NoSQL（DynamoDB）表反规范化？

Question #

I've built a simple API using AWS Lambda, API Gateway, and DynamoDB (this applies to other NoSQL databases like MongoDB too) for tracking workout plans and their associated exercises. Workout plans have a one-to-many relationship with exercises. My API has two endpoints:

• /workouts/{id}: Returns the workout plan as a JSON object
• /workouts/{id}/exercises: Returns the exercises associated with that workout plan

I’m wondering: Should I denormalize my NoSQL (DynamoDB) table if I need to query this data independently?

Answer #

Great question—this is one of the most common tradeoffs you’ll tackle when working with NoSQL databases, especially DynamoDB. Let’s break this down based on your specific use case and access patterns:

1. What denormalization would look like for your setup #

Denormalization here would mean embedding the full exercise data directly inside each workout plan item. For example, a DynamoDB item for a workout might look like this:

{
  "id": "workout_123",
  "name": "Upper Body Push Day",
  "exercises": [
    { "id": "exercise_456", "name": "Bench Press", "sets": 4, "reps": 8 },
    { "id": "exercise_789", "name": "Overhead Press", "sets": 3, "reps": 10 }
  ]
}

This way, your /workouts/{id} endpoint can fetch everything in a single read operation, and /workouts/{id}/exercises can just extract the exercises array from the same item.

2. When denormalization makes sense #

• Frequent combined reads: If your primary use case is users fetching a workout plan and immediately needing its exercises (or vice versa), denormalization cuts down on the number of read operations. This boosts performance and reduces costs in DynamoDB, where you’re charged per read capacity unit.
• Rare exercise updates: If your exercises don’t change often (e.g., you rarely modify an exercise’s name or set/reps defaults), the overhead of updating multiple workout items when an exercise changes is minimal. You can accept the extra write cost for better read performance.

3. When sticking with a normalized approach is better #

• Independent exercise queries: If you ever need to query exercises on their own (e.g., "find all workouts that include Bench Press" or "get details for exercise_456 across all plans"), a normalized structure (separate Workouts and Exercises tables, linked by workout_id) is far more flexible.
• Frequent exercise updates: If you regularly tweak exercise details, normalization avoids the headache of updating every workout item that includes that exercise. This eliminates the risk of data inconsistency (e.g., one workout still showing an old exercise name while others are updated).
• Your existing endpoints work fine with normalization: For your /workouts/{id}/exercises endpoint, you can create a Global Secondary Index (GSI) on the Exercises table using workout_id as the partition key. This lets you quickly query all exercises for a specific workout with a Query operation—performance is nearly as good as denormalization, but with cleaner data.

4. Hybrid approach: The best of both worlds #

If you have mixed access patterns (you need both fast combined reads and flexible independent queries), consider a middle ground:

• Keep separate Workouts and Exercises tables for clean, maintainable data.
• Add an exercise_ids array to the Workouts table (storing only exercise IDs, not full data). When fetching a workout, your /workouts/{id} endpoint can return the plan plus the list of exercise IDs, and your client can use DynamoDB’s BatchGetItem to fetch all associated exercises in a single additional request.
• Or, use DynamoDB’s TransactWriteItems to ensure atomic updates: when you modify an exercise, you can automatically update all workout items that embed it. This works best if exercise updates are rare, as it adds complexity to your write operations.

Final Takeaway #

The decision boils down to your read/write priorities:

• If combined plan+exercise reads are frequent and exercise updates are rare → Go with denormalization.
• If you need flexibility for independent exercise queries or have frequent updates → Stick with normalized tables + GSIs.

For your current API endpoints, both approaches are viable—denormalization optimizes the /workouts/{id} endpoint, while normalization keeps your data scalable and easy to maintain for future use cases.

内容的提问来源于stack exchange，提问作者Harry