I am trying to create a website for my band where I display our next three upcoming shows. All shows are stored using DynamoDB with three attributes: a date (number that is a unix timestamp), venue (string), and address (string). My first idea was to create a query to return the top 3 results sorted by the date, which I'm used to doing in MySQL. However, it seems sorting can only occur within the same partition, when using a composite key containing a partition_id and a sort_id.
Should I simply put all entries into my table under the same partition_id? This is a very simple website for my band, and it does not need to be performant. However, this seems like an unusual solution in order to simply sort by the date.
I am new to DynamoDB, and I'm trying to figure out the best way to structure my database. Any recommendations would be appreciated.
Just store your data with a static partition key, as you are only showing a limited amount of data and updates to the data won't exceed more than 1000 RPS.
| PK | SK | VENUE | ADDRESS |
|---|---|---|---|
| 1 | 2023-04-05T21:00 | Big Club | Big Town |
| 1 | 2023-05-15T23:00 | Med Club | Med Town |
| 1 | 2023-07-05T21:00 | Festival | New Town |
Now everything is stored in ascending order by date.
To do a request to show your next 3 upcoming shows, it would look like this:
SELECT * FROM mytable WHERE PK = 1 AND SK > todays_date_string
Since you're familiar with SQL you can even use PartiQL to make the requests in SQL language.