How to design a DynamoDB table schema

user_14342054 0
Viewed 242

I am doing my best to understand DynamoDB data modeling but I am struggling. I am looking for some help to build off what I have now. I feel like I have fairly simple data but it's not coming to me on what I should do to fit into DynamoDB.

I have two different types of data. I have a game object and a team stats object. A Game represents all of the data about the game that week and team stats represents all of the stats about a given team per week.

A timeId is in the format of year-week (ex. 2020-9)

My Access patterns are

1) Retrieve all games per timeId
2) Retrieve all games per timeId and by TeamName
3) Retrieve all games per timeId and if value = true

4) Retrieve all teamStats per timeId
5) Retrieve all teamStats by timeId and TeamName

My attempt at modeling so far is:

PK: TeamName
SK: TimeId

This is leading me to have 2 copies of games since there is a copy for each team. It is also only allowing me to scan for all teamStats by TimeId. Would something like a GSI help here? Ive thought maybe changing the PK to something like

PK: GA-${gameId} / TS-${teamId}
SK: TimeId

Im just very confused and the docs aren't helping me much.

amazon-dynamodb nosql amazon-web-services database-design
1 Answers

Looking at your access patterns, this is a possible table design. I'm not sure if it's going to really work with your TimeId, especially for the Local Secondary Index (see note below), but I hope it's a good starting point for you.

# Table
-----------------------------------------------------------
pk       | sk                   | value | other attributes
-----------------------------------------------------------
TimeId   | GAME#TEAM{teamname}  | true  | ...
TimeId   | STATS#TEAM{teamname} |       | ...
GameId   | GAME                 |       | general game data (*)
TeamName | TEAM                 |       | general team data (*)
 
# Local Secondary Index
-------------------------------------------------------------------------------
pk from Table as pk | value from Table as sk | sk from Table + other attributes
-------------------------------------------------------------------------------
TimeId              | true                   | GAME#Team{teamname} | ...

With this Table and Local Secondary Index you can satisfy all access patterns with the following queries:

  1. Retrieve all games by timeId:

    Query Table with pk: {timeId}

  2. Retrieve all games per timeId and by TeamName

    Query table with pk: {timeId}, sk: GAME#TEAM{teamname}

  3. Retrieve all games per timeId and if value = true

    Query LSI with pk: {timeId}, sk: true

  4. Retrieve all teamStats per timeId

    Query table with pk: {timeId}, sk: begins with 'STATS'

  5. Retrieve all teamStats by timeId and TeamName

    Query table with pk: {timeId}, sk: STATS#TEAM{teamname}

*: I've also added the following two items, as I assume that there are cases where you want to retrieve general information about a specific game or team as well. This is just an assumption based on my experience and might be unnecessary in your case:

  1. Retrieve general game information

    Query table with pk: {GameId}

  2. Retrieve general team information

    Query table with pk: {TeamName}

Note: I don't know what value = true stands for, but for the secondary index to work in my model, you need to make sure that each combination of pk = TimeId and value = true is unique.

To learn more about single-table design on DynamoDB, please read Alex DeBrie's excellent article The What, Why, and When of Single-Table Design with DynamoDB.

user_1586680
Related