I am all new to NoSQL and specifically DynamoDB single table design. Have been going through a lot of videos and articles on the internet regarding the single-table design and finally I have put together a small design for a chat application which I am planning to build in the future.
The access patterns I have so far thought about are -
- Get user details by User Id.
- Get list of conversations the user is part of.
- Get list of messages the user has created
- Get all members of a conversation
- Get all messages of a conversation
Also want to access messages of a conversation by a date range, so far I haven't figured out that one.
As per the below design, if I were to pull all messages of a conversation, is that going to pull the actual message in the message attribute which is in the message partition?
Here is the snip of the model I have created with some sample data on. Please let me know if I am in the right direction.
CodePudding user response：
As per the below design, if I were to pull all messages of a conversation, is that going to pull the actual message in the message attribute which is in the message partition?
No, it will only return the IDs of a message as the actual content is in a separate partition.
I'd propose a different model - it consists of a table with a Global Secondary Indexe (GSI1). The layout is like this:
Base Table:
- Partition Key: PK
- Sort Key: SK
Global Secondary Index GSI1:
- Partition Key: GSI1PK
- Sort Key: GSI1SK
Base Table
GSI 1
Access Patterns
1.) Get user details by User Id.
GetItem on Base Table with Partition Key = PK = U#<id> and Sort Key SK = USER
2.) Get list of conversations the user is part of.
Query on Base Table with Partition Key = PK = U#<id> and Sort Key SK = starts_with(CONV#)
3.) Get list of messages the user has created
Query on GSI1 with Partition Key GSI1PK = U#<id>
4.) Get all members of a conversation
Query on Base Table with Partition Key = PK = CONV#<id> and Sort Key SK starts_with(U#)
5.) Get all messages of a conversation
Query on Base Table with Partition Key PK = CONV#<id> and Sort Key SK starts_with(MSG#)
6.) Also want to access messages of a conversation by a date range, so far I haven't figured out that one.
DynamoDB does Byte-Order Sorting in a partition - if you format all dates according to ISO 8601 in the UTC timezone, you can make the range query, e.g.:
Query on Base Table with Partition Key PK = CONV#<id> and Sort Key SK between(MSG#2021-09-20, MSG#2021-09-30)