How to design a GSI for this DynamoDB table?-CodePudding

This is a sample question for an AWS certification that I'm trying to clarify in my head. The question is asking that In order to be able to create a leaderboard where I can query the TopScores by User or by Game, I need to update this table to support this new ask:

A popular multiplayer online game is using an Amazon DynamoDB table named GameScore to track users’ scores. The table is configured with a partition key UserId and a sort key GameTitle as shown in the diagram below:

The answer is naturally a GSI since its an existing table but the answer goes to suggest creating an Index called GameTitleIndex which contains GameTitle and TopScore

I feel that this is incorrect since if I create a GSI with JUST TopScore - the primary keys are already projected (so it would already contain UserId and GameTitle).

What do folks suggest?

CodePudding user response：

It's not about whether the primary keys are projected into GSI( they will be) but the real point of having an index is to query on attributes other than the primary key of the base table.

In other words After creating GSI, UserID, and GameTitle even though they will be projected but UserId won't be the primary key or GameTitle would be Sort Key in the GSI ( of course they won't be).

Let's say you have such requirements:-

Find the top score for the game Galaxy Invaders?

Which user has the highest score for Galaxy Invaders?

How are you going to query GSI based on just TopScores, this would be meaningless. However, if you have GameTitle as pk and Scores as the sort key for the GSI, you can easily query based on gametitle and find the highest scores, and even the user who has the highest score in that game.

You should try to remember the original requirement of the question Create a leaderboard where I can query the TopScores by User or by Game.

docs for query operation for better understanding how query helps in fetching multiple records based on pk

CodePudding user response：

Think about your access pattern. If the score is made the partition key you have no way to express the query for top scores of a given game. Just because the attribute is projected doesn’t mean it’s suitably indexed.