DynamoDB single table design for both an "amount" item and separate "amount update entry" items

Question

I am new to DynamoDB and trying to model/index a single table design for tracking a single number value, but with separate update entries for each time the number is changed. Every time the number value is updated, an update entry should be saved to the table (as its own item, not as a property on a single "tracked number" item), and of course the tracked number value should be updated too. I want to be able to query and sort the update entries by date and number change value. And of course I want to be able to look up the current number value.

What is a good single table design for this data and access pattern? I have tried a couple different ways, but find myself blocked because either unable to get/write to a global secondary index, or always returning the item with the current number value when attempting to query on just the update entries themselves. I could very easily create a separate tables for the tracked number and number updates, but that seems to go against DynamoDB principles.

Dennis Traub Dennis Traub · Accepted Answer · 2020-12-19T14:32:24

You could create a table with two types of entries:

The current amount with the value's ID as partition key, the literal string CURRENT_AMOUNT as sort key, and an an attribute current_amount that contains the actual value.
The update entry with the value's ID (same as in 1.) as partition key, the timestamp as sort key, and two attributes new_amount and old_amount to represent the changed values.

This way, you can retrieve:

The current amount of an item: pk={{ID}} AND sk="CURRENT_AMOUNT"
The history of an item: pk={{ID}} AND sk <> "CURRENT_AMOUNT"
Both the current amount and the history of an item at once: pk={{ID}}

Additional access patterns can potentially be satisfied using secondary indexes.

Here's an example of what the table could look like with some entries:

 -----------------------------------------------------------------------
| pk  | sk                   | current_amount | new_amount | old_amount |
 -----------------------------------------------------------------------
| ID1 | "CURRENT_AMOUNT"     | 7              |-------------------------|
|     | 2020-12-19T14:01:42Z |----------------| 7          | 5          |
|     | 2020-12-17T19:07:32Z |----------------| 5          | 9          |
 -----------------------------------------------------------------------|
| ID2 | "CURRENT_AMOUNT"     | 3              |-------------------------|
|     | 2020-12-19T08:01:12Z |----------------| 3          | 7          |
 -----------------------------------------------------------------------

DynamoDB single table design for both an "amount" item and separate "amount update entry" items

1 Answers