Dynamo DB ERD for Chat application

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.

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.

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#))

Supported access patters:

1. Get all rooms of a user (PK=USER#uid && SK=Begins_with(ROOM#))

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!

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.

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)