amazon-web-servicesamazon-dynamodbnosql

How to Optimize DynamoDB Data Model. Please advise

I'm currently working on a DynamoDB data model for an application that manages workout plans - to try get more knowledge of DynamoDB.

Each plan has multiple workouts, and each workout has multiple exercises. Here's my current table structure:

PK SK Other Attributes
PLAN PLAN#001 PlanTitle, PlanDescription, etc.
PLAN PLAN#002 PlanTitle, PlanDescription, etc.
PLAN PLAN#001#WORKOUT#001 WorkoutName, WorkoutDetails, etc.
PLAN PLAN#001#WORKOUT#002 WorkoutName, WorkoutDetails, etc.
PLAN PLAN#002#WORKOUT#001 WorkoutName, WorkoutDetails, etc.
PLAN PLAN#001#WORKOUT#001#EXERCISE#001 ExerciseName, MuscleGroup, etc.
PLAN PLAN#001#WORKOUT#001#EXERCISE#002 ExerciseName, MuscleGroup, etc.

Issue: The current structure uses the same partition key (PK = PLAN) for all items, regardless of whether they are a plan, workout, or exercise. The structure doesn’t clearly reflect the relationships between plans, workouts, and exercises - I can't distinguish them to make the proper requests to retrieve them.

Desired Access Patterns:

I'm new to DyanamoDB structuring and any help would be greatly appreciated!

Attempted to restructure the PKs and SKs

Solution

  • Take the leading part of your sort key and make it your partition key.

    PK SK Other Attributes
    PLAN#001 PLAN#001 PlanTitle, PlanDescription, etc.
    PLAN#001 WORKOUT#001 WorkoutName, WorkoutDetails, etc.
    PLAN#001 WORKOUT#002 WorkoutName, WorkoutDetails, etc.
    PLAN#001 WORKOUT#001#EXERCISE#001 ExerciseName, MuscleGroup, etc.
    PLAN#001 WORKOUT#001#EXERCISE#002 ExerciseName, MuscleGroup, etc.

    Retrieve all plans with their metadata (e.g., PlanTitle, PlanDescription).

    Serve this from a global secondary index where the partiton key is plan title, meaning the index will only contain the root plan info, and you can scan to get all plans.

    Retrieve all workouts for a specific plan.

    SELECT * FROM table WHERE PK = PLAN#001

    Retrieve all exercises for a specific workout

    SELECT * FROM table WHERE PK = PLAN#001 AND SK begins_with WORKOUT#001