how do i design a dynamodb table schema?

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.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top