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:

6. Retrieve general game information

   Query table with pk: {GameId}

7. 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.