Many-to-many access pattern in DynamoDb

user_12482789 0
Viewed 32

I have a model where a user can access multiple files and a file can belong to many users, hence the many-to-many structure. I'm interested in using DynamoDb for this, even though there is no native relational support. From other posts, I think I can start with the following structure:

  1. PK: USER#, SK: USER# = defines a user
  2. PK: USER#, SK: FILE# = attributes for the file shared to a user
  3. PK: FILE#, SK: FILE# = attributes for a file

With this structure, I can do things like "list all the files available to a user" via the pseudo-sql code SELECT * FROM table WHERE PK=user AND SK.startsWith("FILE#").

However I'm not sure how I could do something like "retrieve the file X for user Y" via this structure. This seems to involve two queries:

  1. Retrieve all files available to the user Y and check that X is indeed available
  2. If step 1 confirms that X is available to Y then perform a query SELECT * FROM t WHERE PK=FILE#X and SK=FILE#X. Otherwise fail.

This seems to defeat the purpose of the structure, which is minimising IOPS. How do I go about solving this problem and only requiring one query for this access pattern?

dynamodb-queries amazon-dynamodb many-to-many
1 Answers

You either do two requests or you denormalize the file data into the item held under the user.

Or you could create a list of shared-with users within the file item, but that’s usually not going to scale well.

user_5386970
Related