Just another dynamodb single table design question (YADSTD)

Question

This image represents a model design to get data from a relationship between movies, actors, and directors.

From my understand from the MOV#xyz1234 partition key, they use movieId as part of the primary key to store movies, actors and directors. So for entity movie "PK = MOV#movieId and SK = MOV#movieId" this way if you want to get a movie details you can query by:

PK START_WITH MOV# AND SK = MOV#movieId

And then to store an actor they will use "PK = MOV#movieId and SK = ACT#actorID" this way to get all actors from a movie the query could be:

PK = MOV#movieId and SK START_WITH ACT#

Or if we want an actor details we could query as:

PK START_WITH MOV# AND SK = ACT#actorId

But I get confuse when then I found ACT#aac1004 partition key and DIR#bce0557. My question is: when they created a movie, they stored more than one item at the same time for each entity? How do they store actors and directors. If a movie has a lot of actors, then how to get here:

Answer 1

The rows with MOV# as PK and ACT# establish the relationship that an actor was in a particular movie and may contain attributes about that actor's specific relationship with the movie (i.e. character name, title of movie, etc).

The rows with ACT# as the PK will contain the actor information unrelated to movies (e.g. Date of birth, Bio, etc)

One thing to note in your question is you can't query a PK with "starts with" and that is why a GSI (Global Secondary Index) is added on the SK column so you can query that directly without knowing what the PK column is. The GSI's PK is the SK column so you can query that directly.

So to correct some of the things in your question, if you want to get movie details you would query the normal PK with:

PK = MOV#movieId AND SK = MOV#movieId

If you wanted actor details we could query:

GSI PK = ACT#actorId

And this will return the actor row (PK = ACT#actorID) with actor info as well as all of the rows where PK = MOV# where the actor is the SK.

I'm not sure if this answers the question but hopefully sheds some light on how single table designs work a little better.