0

Need some suggestions in choosing DynamoDB partition key and sort key to retrieve all public and private messages in a chat.

In the same chat room, users can send public messages and also private messages to specific people.

DynamoDB model:

For public messages: hashkey - chatId; sortKey - timestamp
For private messages: hashkey - chatId; sortKey - userId#timestamp

I'm facing difficulty in choosing primary keys which will return both these type of messages in a single query.

NoSQLKnowHow
  • 4,449
  • 23
  • 35
Abie
  • 81
  • 5

1 Answers1

0

Off the top of my head I am thinking that you need something in your sortkey so you can use Begins_with and get all the messages. So something like mssg::public::userID::timestamp or mssg::private::timestamp.

Then you could do a query using chatId as the partition key and then use begins_with mssg on the sort key. That'd get all messages for that chatID whether they are public or private. The next question is what are you doing with the timestamp and do you need to do something with that in this same query? You did not mention it in your post, but it is in the model example.

NoSQLKnowHow
  • 4,449
  • 23
  • 35
  • If I use BEGINS_WITH with mssg, I would get all messages (including private messages of other users) right ? And I’m afraid I won’t be able to store public msgs with userId. Since the number of receivers for public messages might be huge and I won’t be to create a separate record for each userId for a public message. And yes, timestamp will also be required since I need my results sorted by timestamp – Abie Jul 21 '20 at 19:29
  • it might not have userId. for public message, the sortKey might only be mssg::public::timestamp – Abie Jul 21 '20 at 19:35
  • So users can be in the chat room anonymously? I feel like I am missing some important information here to be able to help. – NoSQLKnowHow Jul 21 '20 at 19:54