DynamoDB single query to retrieve both public and private chat messages

Question

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.

Answer 1

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.