Modelling multiple many-to-many relationships in Dynamodb

Question

I am trying to model the following relationship in a single Dynamodb table.

User -> many to many -> Game

User -> many to many -> Group

In words:

• A user can have many games, and multiple users can have the same game
• A user can be in many groups, and a group consists of many users

I have identified the following access patterns:

• List all the games a user owns
• List all the groups a user is in
• List all the members of a group
• List all the games that are owned by members of a group

The first three access patterns can be implemented using the adjacent list design pattern as below:

| Partition Key | Sort Key (GSI Partition Key)  |
| ------------- | ----------------------------- |
| User-<ID>     | Game-<ID>                     |
| User-<ID>     | Group-<ID>                    |

Any recommendations on how to implement the last access pattern - list all games that are owned by members of a group?

Thanks.

Answer 1

Your last query, list all games owned by members of a group, is already possible, but it’s a 2-part query.

First, get a list of the users in the group, then list all of the games for all of those users. You may have some duplicates, which you can clean up in the data access layer of your application.