DynamoDB modelling question: table with games between player1 and player2, how to get all games involving a given player

Question

Am storing games in a database. Games are between two players: call them player1 and player2. I have a document per game, with keys 'player1' and 'player2' containing the player ids. Obviously a given player could appear in either the player1 or player2 key depending on the draw.

Is there a way to structure my data so that I can efficiently find all games for a given player? I know that a query where player1=playerId OR player2=playerId is not possible in dynamo. Am looking for ideas on how to manage it. I started by creating "linked" documents with playerId as the partition key and date/time of game as the sort key. But this is getting messy!

Maybe my best option is to create two GSIs (on player1 and player2) and do an application level union.

Thanks

Answer 1

If I'm reading your question correctly, your access patterns are

1. Fetch games for a player
2. Fetch most recent game for a player (based on comments on an earlier post)

Let's start by modeling the relationship between two players playing a game. I'll call it a Match (naming is hard). You could store the Matches between players like this:

I've made up a few attributes on the Match item to illustrate the concept. I'm using a simple primary key with the format MATCH#[KSUID]. If you're not familiar, a KSUID is a unique identifier that is sortable and has a built-in time component. You can use them like UUIDs, but get the useful side effect of sortability based on time. This feature of KSUIDs will be useful when retrieving the latest Match.

We can create two secondary indexes to model the matches from either players perspective. For example, I'll create a secondary index named Player1Index and give it a GSIPK of the player1 attribute, and the GSISK could be the PK of your main table. Using the example above, your data would look like this

Similarly, the Player2Index would look like this

Notice that the KSUID is part of the sort key in both indexes, which means fetching matches by a player will automatically sort the matches in the order they were created. This would allow for your second access pattern where you fetch the latest match for a given player.

EDIT: If the goal is to get all matches where a given player was player1 or player2, you could create a Match item collection that contains each player as a separate item within the collection. For example:

Then you could create an inverted secondary index, swapping the PK/SK patterns in the index. That would look like this:

In this model, the secondary index would contain all matches for a given player, regardless of their role in the match. You may prefer this solution since you could grab the data in a single query with a single index. Pagination would be easier than the first approach.

Whichever path you take, the goal is to pre-join the data you need so it can be fetched in a single query. Sure, you could use the former pattern and query two indexes, and merge results in your application. Making two queries (versus one) isn't the worst thing in the world, but is way less satisfying than fetching the data all at once!