Table design question - DynamoDB the right choice?

Question

Our team has started to use AWS and one of our projects will require storing approval statuses of various recommendations in a table.

There are various things that identify a single recommendation, let's say they're : State, ApplicationDate, LocationID, and Phase. And then a bunch of attributes corresponding to the recommendation (title, volume, etc. etc.)

The use case will often require grabbing all entries for a given State and ApplicationDate (and then we will look at all the LocationId and Phase items that correspond to it) for review from a UI. Items are added to the table one at a time for a given Station, ApplicationDate, LocationId, Phase and updated frequently.

A dev with a little more AWS experience mentioned we should probably use State+ApplicationDate as the partition key, and LocationId+Phase as the sort key. These two pieces combined would make the primary key. I generally understand this, but how does that work if we start getting multiple recommendations for the same primary key? I figure we either are ok with just overwriting what was previously there, OR we have to add some other attribute so we can write a recommendation + the recommendation status for the State+ApplicationDate/LocationId+Phase multiple times and get all previous values if we need to... but that would require adding something to the primary key right? Would that be like adding some kind of unique value to the sort key?

Does this sound like a reasonable approach or should I be exploring a different NAWS offering for storing this data?

Answer 1

In your scenario you will have to use a primary key such as : State+ApplicationDate and something distinct for a sort key . It seems like a reasonable approach. Think your access patterns (how will you retrieve these data) and if the primary/sort key design meets your needs that is great. And yes, if you use the same primary+sort key combination the old entry will be overwritten. But you could also use a secondary sort key that is ApplicationDate+LocationId+Phase .

On the second part of your question (whether we need a different database service) this is again up to the access pattern you want to facilitate . If the dynamo one meets your needs no reason to investigate further . If you have a different use case such as: I need to store 500 entries every second maybe a database which is built specifically for that will suit you better . Have a look at this.

You could also have a look at this article that explains how dynamo single table design works and some differences with relational databases.