1

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 -

  1. Get user details by User Id.
  2. Get list of conversations the user is part of.
  3. Get list of messages the user has created
  4. Get all members of a conversation
  5. 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.

Single Table Design - Table layout

Maurice
  • 11,482
  • 2
  • 25
  • 45
Sumchans
  • 3,088
  • 6
  • 32
  • 59

1 Answers1

3

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

Base Table

GSI 1

GSI1

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)

Maurice
  • 11,482
  • 2
  • 25
  • 45
  • Thanks Maurice for the detailed explanation. Just want to know what would be more performant and economical. What if when a message is created, it's written both to the user partition and the conversation partition? This way also the 3rd access pattern works. – Sumchans Sep 26 '21 at 01:16
  • What's your use case for that access pattern? That's the one I understood least. From an economics perspective it shouldn't make much of a difference - you're duplicating the storage costs and read/write accesses in both cases. That's why I'd pick the solution with less overhead - i.e. GSI. – Maurice Sep 26 '21 at 09:58
  • I have a feature planned to show the stats like the number of messages sent by user by week, month, etc, if the user is a member of a group chat. That would be the main user case. – Sumchans Sep 26 '21 at 14:39
  • 1
    In that case it's probably better to store an item with the aggregate stats, connect a Lambda to the DynamoDB Stream and have that Lambda update the stats when new messages are created/deleted. That's cheaper. – Maurice Sep 27 '21 at 06:14
  • Okay sounds good. Thanks Maurice. – Sumchans Sep 27 '21 at 13:34