So I have to model info similar to this, https://www.bundesliga.com/en/bundesliga/matchday. When a club is chosen the matches played by the club should be displayed.
A match will basically have two teams playing in it.
{ home_team: 'A', away_team: 'B' }
Suppose I model this in dynamodb with two keys like this,
pk: home_team
sk: datetime
gpk1: away_team
How would I query the list of matches played by a particular team? Team I query can be in pk or gpk1, I believe it's not possible to give 'OR' condition in dynamodb across two keys as well.
I don't want to use filter-expressions, since that just happens on the client side after the data is fetched from db.
My best bet doing two queries at once with pk and gpk1 and merging the results. Just wondering, if there's a better way to model this to avoid merging client side.
You can choose to do two queries like you’ve modeled there or (at write time) do two inserts of the game into the base table, one for each participating team as the PK. So basically the base table would store two points of view of the same game, a copy kept for quick lookup by each team.
Both designs use the same amount of storage and write capacity, interestingly. It’s just with the GSI you get the second write implicitly but have to join two queries.
You can either do a little extra work on the reads or on the writes to make the other simple.