I'm planning on moving a few features of our website into DynamoDB. Each user have a messages board, where other users can send messages to them.
Our mysql table looks like this: ID | AUTHORID | MESSAGE | USERID | TIMESTAMP
What would be the best Partition key and sort key in this case? I didn't understand this concept yet.
On mysql the primary key is ID, and there is an index on USERID, so we can fetch all messages of a given user, and order by ID.
Thanks. Best Regards
Depending on how many messages you'll have per user (ie. if there will be relatively few messages per user), you may select the UserID to be the hash (or partition) key and the ID (or even Timestamp) to be the range key. The primary requirement is that the combination of User ID and ID or User ID and Timestamp be unique, and the other requirement is that the distribution of elements within each hash key be uniform (ie. all user should have about the same number of messages)
This will work well as long as you don't need to make queries against a specific message, without knowing it's associated user. This can be mitigated in your application's logic by always keeping track of the user context, or you can modify the ID to be a composite value that consists of the user id and the numeric value: so a message id would look like this: "123,34" where "123" is the user id and "34" is the message id.
Another possible option is to use the message ID as the hash key and create a Global Secondary Index (GSI) with a partition key on User ID.
I recommend that you read this document that gives guidelines for working with tables and specifically about working with partitions.
