DynamoDb many to many update replicated data

user_4615055 0
Viewed 74

I am creating an application to store bookmarks and every bookmark might have many labels. I am new to NoSQL databases and can't figure out how to structure my table. For now I have the following model:

PK        SK           data
USER#1    USER         some data
USER#1    BOOKMARK#1   bookmarkDetails 
USER#1    LABLE#1      labelDetails
LABEL#1   BOOKMARK#1   bookmarkDetails

With it I can query all user bookmarks and all user labels. But I need to store in the bookmark details which labels are assigned including title and color for example. But what happens when I want to update details of the label and change the name? Should I go through all bookmarks and update the title of the label where it exists? And in the case of DynamoDb that would mean I need to send many request to fetch the bookmarks that I need to update and then send update request.

Is it okay to store the labels that are assigned to a bookmark in bookmarkDetails and store the bookmarks that have the given label in labelDetails? This will lead to the need to update many rows if I change the title of the label or title of the bookmark. Coming from an RDS world this seems overwhelming and huge development effort. Is there another way to represent ManyToMany relationship? In this case I need to be able to fetch all bookmarks by label and that's why I am following this approach.

EDIT: I am adding the access patterns that I will need here to be more clear.

Entities:
User: ID and email
Bookmark: ID and url
Label: ID and title

Relations: 
User to Bookmark = OneToMany
Bookmark to Label = ManyToMany
  1. Fetch all bookmarks by user ID - including details about all the labels that are attached to a given bookmark
  2. Fetch all labels by user ID
  3. Fetch all bookmarks by label ID - including information about all labels that are attached to the given bookmark

And the update actions are to be able to change the label title and bookmark url

amazon-dynamodb nosql
1 Answers

Storing labels data in bookmarkDetails and bookmarks data in labelDetails will complicate the solution. It might be better to separate out the details of bookmarks and labels into separate tables and just keep the mappings of label-bookmark-user in one table.

Whether or not the following solution works will depend on the number of distinct bookmarks you have, number of distinct labels, number of distinct users, number of bookmarks per user and number of labels per bookmark.

Table 1: Bookmark Details

PK 
----------
BookmarkID     BookMarkDetails (Name, title etc)

Table 2: Label Details

PK
-------
LabelID     LabelDetails (Name, title etc)

Table 3: UserBookmarkLabelMapping

PK                    SK        
-----------------  --------
UserID#BookmarkID   LabelID(GSI)    UserID (GSI)    BookmarkID (GSI)

Write Operations

  1. Bookmark Details Update: Will do one write in Table 1.
  2. Label Details Update: Will do one write in Table 2.
  3. Creating a new label/ Deleting a label/ Modifying a label for a bookmark for a user: one write in Table 3.

Read Operations

  1. Get all user bookmarks:
  • Query on the UserID-GSI. One Read operation.
  • Get details of all bookmarkIDs from Table 1. One BatchGet operation (if < 100 bookmarks per user) Else multiple BatchGet operations in parallel

  1. Get all user labels: Same as above.

  2. Get all bookmarks for a label.

  • Query on LabelID-GSI to get all BookmarkIds. One Read Operation.
  • Get details of all these bookmarkIds from table 1. One BatchGet operation (if < 100 bookmarks per label) Else multiple BatchGet operations in parallel.

The drawback is that unlike the relational world we will be running two sequential queries - one to get the Ids and second one to get the details of these IDs. This can be overcome if the number of distinct bookmarks/labels is small, we can cache this data in memory. Or if you are using DynamoDB, then maybe using something like DAX to speed up the getBookmarkDetails, getLabelDetails calls.

user_73207200
Related