1

I am trying to migrate my chat application from Firestore to DynamoDB.

Currently I have 2 collections in firebase: Rooms and Users. Users collection have all roomsIds the user is part of. Rooms collection have some meta data such as name, icon etc. along with Messages sub-collection. Messages sub-collection have the entire payload related to a message.

I am confused when designing my Dynamo DB ERD for the same. I have developed the following ERD along with access patters listed, but I am unable to figure out the PK, SK and Secondary index if needed.

ERD

Update 06/14/2021: I have managed to have a following design based on the answers and help I received which I think will support all my access patters.

One major query I want to run is to fetch all room's config items by userid, I am not sure if I will be able to fetch that in a single query. Right now the easy solution is to fetch all rooms of a user then fetch all config(SK) for all the fetched rooms, which I think is not a good solution.

Table 1

Supported access patters:

  1. Get All Users in a room (PK=ROOM#rid && SK=Begins_with(USER#))
  2. Get Config of a single room (PK=ROOM#rid && SK=CONFIG)
  3. Get Messages of a single room (PK=ROOM#rid && SK=Begins_with( MESSAGE#))

GSI

Supported access patters:

  1. Get all rooms of a user (PK=USER#uid && SK=Begins_with(ROOM#))
Abhigyan Singh
  • 137
  • 3
  • 14

1 Answers1

5

Good job modeling the entities and listing the access patterns. That's the first step in being successful with NoSQL data modeling!

I'm going to outline one way you could model this application. Keep in mind this is not the only way to model the access patterns for this application, and it likely doesn't handle all the edge cases you'll have. However, I hope this example gets you un-stuck!

Here is how I defined the PK/SK patterns in the base table. Notice that I added a type field to each item. Since each item can be any type, I like to add this field to help me understanding what each item represents!

enter image description here

This table supports the following access patterns:

  • fetch rooms for user (Query for PK=USER#userId SK beings_with ROOM)
  • fetch message by ID (Get Item where PK=MSG#messageId SK=MSG#messageId

I chose to use a KSUID for the message ID. KSUIDs are unique identifies that are sorted by the time they were created. This will be useful when we fetch messages for a room.

I also created a Global Secondary Index named GSI1 on attributes GSI1PK and GSI1SK.

enter image description here

GSI1 supports:

  • fetch messages by room, sorted by creation date (Query GSI1 for PK=ROOM#roomId SK begins_with MSG#). The messages will come back sorted by creation date/time since we're using KSUIDs!
  • fetch all users by room (Query GSI1 for PK=ROOM#roomId SK begins_with USER)
Seth Geoghegan
  • 5,372
  • 2
  • 8
  • 23
  • Hi @Seth, Thanks a lot for the great explanation. It really helped me in understanding it better. I have updated my answer based on your inputs. Now only need to take care of one last access patern – Abhigyan Singh Jun 14 '21 at 05:46
  • You have a many-to-many relationship in your data model between Users and Rooms. Your initial list of access patterns only required to read one side of that relationship (fetch Users per room). Including this side of the relationship will change the data model. There are several strategies for modeling many-to-many relationships. The one you choose depends on the details of your access pattern. Does your application need all the room details for each user, or would the room name and room ID be sufficient? – Seth Geoghegan Jun 14 '21 at 18:58
  • Yes correct, I need all the room's item with `SK=CONFIG` for a particular user to show the name and icon. But I think what I can do is to fetch all rooms by user using GSI, then do a `BatchGetItem` to get all room's `CONFIG` items – Abhigyan Singh Jun 15 '21 at 06:01
  • 2
    If your application only needs to show the room name and icon, you could duplicate those fields to the User item (PK=ROOM#rid SK=USER#uid) when you add the User to the room partition. By doing this, you'll ensure the room fields will be available available in GSI1. This is a useful pattern when the attributes you are duplicating don't change often, which seems like it could be the case for room name and icon. – Seth Geoghegan Jun 15 '21 at 14:19
  • @SethGeoghegan I've been struggling with this very same problem. I need to get all the room config by userID. Could you please tell me what would be the advantages of using GSI overloading instead of just creating a regular table with a GSI where PK = UserID, projecting the needed attributes? – Nathalia Soragge Feb 17 '22 at 07:34