DynamoDB Data Modelling Hands-On

Problem Statement: https://amazon-dynamodb-labs.workshop.aws/scenarios/retail-cart.html

Step 1: Create a DynamoDB table named retail-cart-challenge with partition key as PK and sort key as SK. Also create following indexes

Index_Name                  Index_Type    PK         SK
Search_By_OrderID           LSI           PK         OrderID
Search_Items                GSI           ItemSKU    SK

Step 2: Create following items for user records

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#USERMETADATA#1"
  },
  "Name": {
    "S": "Vinayak"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#USERMETADATA#2"
  },
  "Name": {
    "S": "Rohit"
  }
}

{
  "PK": {
    "S": "AccountID#3"
  },
  "SK": {
    "S": "#USERMETADATA#3"
  },
  "Name": {
    "S": "Virat"
  }
}

{
  "PK": {
    "S": "AccountID#4"
  },
  "SK": {
    "S": "#USERMETADATA#4"
  },
  "Name": {
    "S": "Rahul"
  }
}

and for Product records

{
  "PK": {
    "S": "ItemSKU#1"
  },
  "SK": {
    "S": "#ITEMMETADATA#1"
  },
  "Product_Name": {
    "S": "Shampoo"
  },
  "Quantity": {
    "N": "200"
  }
}

{
  "PK": {
    "S": "ItemSKU#2"
  },
  "SK": {
    "S": "#ITEMMETADATA#2"
  },
  "Product_Name": {
    "S": "Soap"
  },
  "Quantity": {
    "N": "150"
  }
}

{
  "PK": {
    "S": "ItemSKU#3"
  },
  "SK": {
    "S": "#ITEMMETADATA#3"
  },
  "Product_Name": {
    "S": "Rice"
  },
  "Quantity": {
    "N": "350"
  }
}

{
  "PK": {
    "S": "ItemSKU#4"
  },
  "SK": {
    "S": "#ITEMMETADATA#4"
  },
  "Product_Name": {
    "S": "Flour"
  },
  "Quantity": {
    "N": "250"
  }
}

and for users and products mapping

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#ACTIVE#1699346905#ItemSKU#1"
  },
  "ItemSKU": {
    "S": "ItemSKU#1"
  },
  "Item_Name": {
    "S": "Shampoo"
  },
  "Quantity": {
    "N": "3"
  }
}

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#PURCHASED#1698347905#ItemSKU#3"
  },
  "ItemSKU": {
    "S": "ItemSKU#3"
  },
  "Item_Name": {
    "S": "Rice"
  },
  "OrderID": {
    "S": "Order101"
  },
  "Quantity": {
    "N": "4"
  }
}

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#PURCHASED#1698347905#ItemSKU#4"
  },
  "ItemSKU": {
    "S": "ItemSKU#4"
  },
  "Item_Name": {
    "S": "Flour"
  },
  "OrderID": {
    "S": "Order101"
  },
  "Quantity": {
    "N": "1"
  }
}

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#PURCHASED#1699347905#ItemSKU#4"
  },
  "ItemSKU": {
    "S": "ItemSKU#4"
  },
  "Item_Name": {
    "S": "Flour"
  },
  "OrderID": {
    "S": "Order102"
  },
  "Quantity": {
    "N": "2"
  }
}

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#SAVED#1699347905#ItemSKU#1"
  },
  "ItemSKU": {
    "S": "ItemSKU#1"
  },
  "Item_Name": {
    "S": "Shampoo"
  },
  "Quantity": {
    "N": "1"
  }
}

{
  "PK": {
    "S": "AccountID#1"
  },
  "SK": {
    "S": "#SAVED#1699347905#ItemSKU#2"
  },
  "ItemSKU": {
    "S": "ItemSKU#2"
  },
  "Item_Name": {
    "S": "Soap"
  },
  "Quantity": {
    "N": "1"
  }
}


{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#ACTIVE#1799346905#ItemSKU#1"
  },
  "ItemSKU": {
    "S": "ItemSKU#1"
  },
  "Item_Name": {
    "S": "Shampoo"
  },
  "Quantity": {
    "N": "1"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#PURCHASED#1798347905#ItemSKU#3"
  },
  "ItemSKU": {
    "S": "ItemSKU#3"
  },
  "Item_Name": {
    "S": "Rice"
  },
  "OrderID": {
    "S": "Order201"
  },
  "Quantity": {
    "N": "4"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#PURCHASED#1698347905#ItemSKU#4"
  },
  "ItemSKU": {
    "S": "ItemSKU#4"
  },
  "Item_Name": {
    "S": "Flour"
  },
  "OrderID": {
    "S": "Order202"
  },
  "Quantity": {
    "N": "1"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#PURCHASED#1719347905#ItemSKU#4"
  },
  "ItemSKU": {
    "S": "ItemSKU#4"
  },
  "Item_Name": {
    "S": "Flour"
  },
  "OrderID": {
    "S": "Order203"
  },
  "Quantity": {
    "N": "2"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#SAVED#1699347905#ItemSKU#1"
  },
  "ItemSKU": {
    "S": "ItemSKU#1"
  },
  "Item_Name": {
    "S": "Shampoo"
  },
  "Quantity": {
    "N": "1"
  }
}

{
  "PK": {
    "S": "AccountID#2"
  },
  "SK": {
    "S": "#SAVED#1799347905#ItemSKU#2"
  },
  "ItemSKU": {
    "S": "ItemSKU#2"
  },
  "Item_Name": {
    "S": "Soap"
  },
  "Quantity": {
    "N": "1"
  }
}

Step 3: Now let’s run some query as per the scenarios given at https://amazon-dynamodb-labs.workshop.aws/scenarios/retail-cart.html

Access Pattern 1: Return items related to a user (AccountID), sorted by CreateTimestamp, and scoped to a specific Status.

Access Pattern 2: Return items related to a user (AccountID) between a time range and scoped to a specific Status

Access Pattern 3: Return items across user by ItemSKU, sorted by CreateTimestamp, and scoped to a specific Status

Access Pattern 4: Find items in a order placed by a user