Which partition and sort key should I use to store my turn-based strategy game records?

Question

Originally posted this question on stackoverflow but got downvoted and told to come here. Hope this question is ok:

**Context:** Creating a 2 player turn-based strategy game like chess in godot using a websocket and aws apigateway to connect to aws lambda and a dynamodb table.

**Goal:** I would like to allow players to get the specific games of a player for the current version of the game that they are playing i.e. if player A wants to view player B's game, they can send a request to view all of player B's games from Version 1.01 (the current version of the game)

These are the attributes that I was planning on using:

I was originally planning to have Player_One as my partition key and Version_TimeStamp as a composite sortkey. However, this design would mean that I would have to create a GSI to query by Player_Two to get all the games of a player. Additionally, I do not know what I would do if I later wanted to have a 3 player game mode? Make another GSI? Through reading the documentation, I know that GSI's would mean duplicating my table which would be rather costly and something I would prefer to avoid if I could.

The only other solution I can think of is to use Game_ID as my primary key, and no sort key and have a secondary table which stores all the Game_IDs of a player like so:

**PK: Username** | **SK: Version** | Games

where Games would be an array of Game_IDs and I would first get all the game_ids from this table, then I would issue a BatchGetItem command to my initial table to get all the relevant rows of data. However, this approach would mean more dynamodb commands and an additional table. Additionally, it would mean not having a sortkey for my initial table to make BatchGetItem possible. From my (limited) understanding, this would mean that getting items would be slower and more costly as they would no longer be grouped together in partitions by the partition key since each partition key would now be unique. I assume that this solution is better than my initial solution though?

**Research I've done:**

read the aws documentation on best practices for partition and sort keys and multiple blog posts
watched multiple youtube videos on partition and sortkeys
even asked AI for help.
Checked stackoverflow for similar questions but did not find any that provided me with a satisfactory answer

re:Post Agent · Answer

For your turn-based strategy game, I recommend using a single-table design with generic primary key attributes that can be overloaded with different types of values. This approach is considered a best practice in DynamoDB for gaming applications.

Consider using:
- Partition Key (PK): A composite identifier that can represent different entity types
- Sort Key (SK): A value that helps organize and query related items

For your game records, you could structure it like this:

**For game records:**
- PK: "GAME#[GameID]"
- SK: "META"
- Attributes: Player_One, Player_Two, Version, Timestamp, Map, Gamemode, Status, GameRecord

**For player-game associations:**
- PK: "PLAYER#[PlayerID]"
- SK: "GAME#[GameID]#VERSION#[Version]#TIMESTAMP#[Timestamp]"
- Attributes: Role (Player_One/Player_Two/etc.), GameID (for reference)

This design allows you to:
1. Get all games for a specific player by querying with PK="PLAYER#[PlayerID]"
2. Get all games for a player filtered by version using a begins_with condition on the SK
3. Support multi-player games (3+ players) without schema changes by adding more player-game association items
4. Retrieve specific game details using PK="GAME#[GameID]"

You can add a Global Secondary Index (GSI) with:
- GSI PK: "VERSION#[Version]"
- GSI SK: "TIMESTAMP#[Timestamp]"

This would allow querying games by version and sorting by timestamp.

The advantage of this approach is that it eliminates the need for multiple tables or excessive GSIs, while still supporting all your access patterns. It also scales well if you need to add more game modes or player counts in the future.  
**Sources**  
[Gaming profile schema design in DynamoDB - Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/data-modeling-schema-gaming-profile.html)  
[Data modeling example - Best Practices for Migrating from RDBMS to Amazon DynamoDB](https://docs.aws.amazon.com/whitepapers/latest/best-practices-for-migrating-from-rdbms-to-dynamodb/data-modeling-example.html)  
[Should I use a separate DynamoDB table for each App Feature? Or keep it all in one table? | AWS re:Post](https://repost.aws/questions/QUTaYxMjCgSwGGHdE-s1A62A/should-i-use-a-separate-dynamodb-table-for-each-app-feature-or-keep-it-all-in-one-table)

Which partition and sort key should I use to store my turn-based strategy game records?

Add your answer

Relevant content