I have an entity "MyEntity" which has some fields - "StartDate", "EndDate", "Color".
It can have multiple records, and each record is sometimes updated. I need to keep track of what updates were performed on each record and perform an "undo" operation and restore that record to previous state.
I have heard of memento design pattern, but that's mostly in-memory. I need a solution where this information is stored at database level. If i go to a record of MyEntity, and click "Undo" button, it should revert the last update.
Are there any recommendation on best data model on how to store that info in database?
Example,
MyEntityRecord1 (created) -> Jan1-2025, Dec31-2025, Blue
MyEntityRecord1 (updated) -> Jan1-2025, May30-2025, Blue
MyEntityRecord1 (updated) -> Jan1-2025, May30-2025, Red
MyEntityRecord1 (updated) -> Jan20-2025, May30-2025, Purple
MyEntityRecord1 (first undo) -> Jan1-2025, May30-2025, Red
MyEntityRecord1 (second undo) -> Jan1-2025, May30-2025, Blue
Thats the kind of functionality i am looking at. Side note: We cannot undo and undo operation.
Our database stores only the latest state of MyEntityRecord1. For undo to work, i need some sort of diff on what changes were made on last operation.
At the top of my head, I probably need to create another entity in database (MyEntityUpdates), that stores all intermediate states (and reference to the MyEntity record), but not too sure if thats the best design.
We use a relational database, if that information is any use and run SQL queries
You'll probably want some kind of audit/history table. This is a common pattern in some apps, often referred to as "event sourcing" or "audit trails", depending on depth and use case.
MyEntity
| Column Name | Type | Description |
|---|---|---|
| id | INTEGER | primary key |
| start_date | DATE | start date of the entity |
| end_date | DATE | end date of the entity |
| color | TEXT | color of the entity |
MyEntityHistory
| Column Name | Type | Description |
|---|---|---|
| id | INTEGER | primary key |
| entity_id | INTEGER | foregin key to MyEntity(id) |
| start_date | DATE | snapshot start date |
| end_date | DATE | snapshot end date |
| color | TEXT | color of snapshot |
| version | INTEGER | auto increment per entity |
| updated_at | TIMESTAMP | time when the update occurred |
| change_type | TEXT | (optional) what kind of change 'update', 'undo', 'create', etc. |
| performed_by | TEXT | (optional) who made the change (useful depending on use case) |
update to MyEntity triggers insert into MyEntityHistory before the change is applied, storing the pre-change state. Or you can do it after applying and store the new state; both are valid depending on what you want.