Dynamodb many to many


As an example. A user table where many users can be in multible groups

Each Item could look like this.

User: {userId: ‘someid’, name: ‘somename’, groups: {onegroup, anothergroup}

As long as I only need the user by userId this is perfect. Its nice to have the data together. But if I need to query by groups how would you solve this.

I cant add a global secondary index on the groups.

My plan right now is this.
User: {userId: ‘someid’, name: ‘somename’}
UserGroup: {userId: ‘someid’, groupId: aGroupId}
UserGroup: {userId: ‘someid’, groupId: anotherGroupId}
And add a gsi reversed with groupId as primary key and userid as sort.

Now if I want to query all users in a group I can query the UserGroups index. But how would you join this with the data from the users.

I thought about batch get. But somehow it seems wrong :slight_smile:


For anyone else who might run into this one.

Instead of adding the userGroup as a set to user or instead of having it as a seperate table Im going to add it as a flattened array.

So on my user Ill have a userGroups attribute with values like this. groupA_groupB_groupC now I can make an index on the userGroups column and select where userGroups contain the userGroup Im looking for.

Hope it makes sence =)

NoSQL databases don’t really do relationships (especially many to many). Depending on the size of your table and frequency of use you may be able to get away with a scan against the User table where groups includes desired group. Failing that the typical advice for NoSQL databases is to copy the data you need.

Hmm ok buggy Ill think a little more about this… Just found out I cant do my initial plan since contains cant be used on an index key.

So a typical solution to this could be to have multible users with same userid where key could be
userid and sort could be a group.

Then I could create an index with group as key and userid as sort. and when I update a user I would just have to update all of them… Hmm that migth be a solution. They would rarely change once created.

Ok then in this example I would create a user table like this.

userId, group, name, companyId, primary

with data.
userIdkeyValue1, groupOne, aName, companyId1, true
userIdkeyValue1, groupTwo, aName, companyId1, null

and an index on groupId (key), userId(sort) to find users by group.

How would you solve showing one of each user for an admin page ? :slight_smile: