DynamoDB one-to-many and many-to-many structure

Question

I am very new to DynamoDB and trying to understand relations.

I have a todo app with users, lists and items.

I have created 3 dynamoDB tables, one for users, one for lists and one for items.

For simplicity, taking the user/list case. The user's primary key is userId. The list primary key is listId. The user can have many lists. Lists can be shared among users so lists can have many users.

So should list's be saved in the user item as an array of listId's? Then when I get a user, I iterate though the array of listId's and get all the lists?

The user can have many lists which in turn can have many items so I don't want to save the entire list in the user item. Also the list can be shared by many users.

I have tried to search relations but they all seem so start off on the assumption that the reader has a broad idea of NOSQL databases which I don't.

Answer 1

One-to-Many relationships are one of DynamoDB's strengths in my opinion. In your example, there is a One-to-Many relationship between a List and its Items. To model that in Dynamo lets say you have an Item schema that looks like the following

{
  itemId: String,
  listId: String,
  name: String
}

You can create a Item table with a Hash key of itemId. That will allow you to do all of the standard CRUD operations on a single Item.

If we also create a Global Secondary Index with a Hash key of listId and a Sort key of itemId, that will allow us to Query for all of the Items in a given List.

In this case, the Global Secondary Index gives us that One-to-Many relationship between a List and its Items. You can also think of this as grouping Items by their listId.

Many-to-Many relationships are tough. Generally they require you to make a decision between making multiple queries and duplicating data. In either case, a good approach to start out with might be to create a UserList table with a simple schema like the following

{
  userId: String,
  listId: String
}

using userId as your Hash key and listId as your Sort key. You can think of this table as having List IDs grouped by User ID. You can then create a Global Secondary Index using listId as your Hash key and userId as your Sort key. This will give you User IDs grouped by List ID. This table combined with its GSI gives you the Many-to-Many relationship.

Of course, if you use this approach for Many-to-Many relationships, you will need to make requests to the User and/or List tables to get the actual data for those objects. To optimize this, you will need to duplicate the User and/or List data into the UserList table.

There are other approaches to Many-to-Many relationships in Dynamo but this is a good place to start.