CODEWITHSUNDEEP
amazon-dynamodbnosqldynamodb-queries
GreatNews
GreatNews

Reputation: 675

DynamoDB: Avoid duplicating data in many-to-many relationship

I am practicing DynamoDB applying STD (single table design) and I am having some troubles to design my scheme.

Given the simple many-to-many relationship "A Club can have N players while a player can play in M clubs". And supposing the following two AP (access patterns):

  1. Get all the players from a Club.
  2. Get all clubs where a player plays.

The AP 1 can be solved by the following schema:

enter image description here

Pseudo-Query:

GET * WHERE PK = 'CLUB#C1'

However, I am not figuring a good way to solve the AP 2. I could a GSI and have the following new scheme:

enter image description here

But, despite I can get the Clubs IDs, I am not retrieving the Club's information.

I have read AWS's dcumentation the Adjacency list design pattern enter image description here

But, as far as I understand, it is quite different from my example since it is not querying Invoice's specific information for each bill. In my case, I do need the Club's specific information shared by all players.

The only way I have figured out to do so (in a single query) is: for each Club-Player relation, store both entities' information.

Considering that both Club and Player have not static data that may change over time, how would I handle updates? Is it expected to do N updates each time an attribute change?

Upvotes: 1

Views: 122

Answers (1)

Leeroy Hannigan
Leeroy Hannigan

Reputation: 19893

You can't always expect to get all of the data with a single lookup. You want all the clubs that a player plays for, which you can get from your GSI. Then you have 2 choices, wait until a user clicks on a club at fetch that information with a GetItem, or load all the clubs needed beforehand with a BatchGetItem.

Upvotes: 1

Related Questions