Photo by Ricardo Gomez Angel on Unsplash

We’re continuing to work through Jeremy Daly’s 20 “easy” steps to switch from RDBMS to DynamoDB, using Babbl as our example application. In this post we’ll tackle steps three through five - data access patterns.

Step Three

Create a list of ALL your access patterns. If “search” is an access pattern, don’t worry, we’ll deal with that in STEP 17. 😉

As we mentioned previously, we’re building a minimum viable product (MVP), so we want to include only the features required to support that MVP. Let’s think about our app from a user journey perspective.

  • CreateUser - The first thing we do is sign up for our app. The heavy lifting here is handled by Amazon Cognito, but ultimately it will return a unique username and a preferred_language. We’ll need to store those.
  • CreateConversation - Okay, we have a user. The next thing that user wants to do is talk to someone, so we need to create a conversation. This is pretty straightforward: a guid/uuid for the id, now as the date_time_started, and an empty string for the title. If it’s a two-person conversation, each user will see the other’s username as the conversation title. If it’s multi-user, we can prompt for a title in a later feature.
  • CreateParticipation - This happens behind the scenes with CreateConversation or when an additional user is added to an existing conversation. Pretty straightforward many to many stuff: the user we’re adding gets her username added as the user_id, the conversation we’re adding to has its id added as the conversation_id, and we use now as the date_time_joined.
  • CreateMessage - This one is a little more complicated, but nothing too bad. We have two cases:
    • User sending a new message: here we use a guid/uuid for the message id, null as the original_message_id, the user’s username as the sender_id, the conversation’s id as the conversation_id, and the user’s preferred_language as the language. We also need to kick off a background job to translate the message body (We forgot to include this; major oversight!) once into each unique language required by a participant in the conversation - a great use case for AWS Step Functions!.
    • Translated message: once that background task completes, the returned translation should be added as a new message with a new guid/uuid as the id, the original message’s id as the original_message_id, the same user’s username as the sender_id, the conversation’s id as the conversation_id, the translation target language as the language, and the translated text as the body.
  • GetConversationsByUser - Fairly straightforward again. When a user logs in, she should see a list of all conversations she’s participating in sorted by reverse chronological order of the last message received.
  • GetUsersByConversation - When you’re sending text, you want to know who’s receiving it. This lists all users participating in a given conversation.
  • GetMessagesByConversation - The soul of the app. Get a list of all messages in a particular conversation filtered by the user’s preferred_language.

Step Four

Narrow down your access patterns to the ones required by app users. Data analysis and admin tasks, such as analytics, aggregations, behavioral triggers, etc, are all important, but likely not necessary for the end user interacting with your app in real-time. 🚫📊

Well, since we’re targeting an MVP, we need all the ones outlined above. There are some other things we can do later, such as adding read receipts, searching messages, or the ability to delete messages, but for now, we have to keep all of them:

  • CreateUser
  • CreateConversation
  • CreateParticipation
  • CreateMessage
  • GetConversationsByUser
  • GetUsersByConversation
  • GetMessagesByConversation

Step Five

Determine if your user access patterns require ad hoc queries that need to reshape the data. Likely the answer is, no. However, if you’re building an OLAP application, NoSQL is not a good choice. Pat yourself on the back for trying, and use another technology. 🤷‍♂️

No, none of our access patterns require ad hoc queries to reshape the data.

Office Space characters saying 'that was easy.'

Correcting our ERD

As we saw above, we left out a pretty big feature for a chat app: message bodies! Our message entity should look as follows:


  • id - (primary key) a guid/uuid unique to this message
  • original_message_id - (foreign key, nullable) the id of the original message from which this instance was translated, or null if this is the original message
  • sender_id - (foreign key) the username of the user who sent this message
  • conversation_id - (foreign key) the id of the conversation where this message was sent
  • language - the Amazon Translate language code for this message instance
  • body - the message body

And now, our updated ERD looks as follows:

Entity Relationship Diagram showing four entities: User, Conversation, Participation, and Message

Next up - Rick Houlihan!