We're starting to record history in an about 5 y/o internet system built with ASP.NET Core and SQL Server.
Our current requirement is to save all records from a specific table, plus two fields from two other tables.
We've tried using Temporal Tables, but since in some scenarios the user can change history records - that didn't work.
Here are the main requirements we have:
- In some scenarios the users (-admins) can change the history.
- Users can save future records. That is - records that will be relevant in the future (they can say a student will leave a boarding school a week from now).
- Saving the data is performed from several modules (different services - some code and some SQL)
- We will need to record history for many other data points in the future.
So here are the two alternatives we came up with:
- Move the extra two columns from their tables to the main table, and just write every change to that table.
This means all the data will be in one table. Since there are future records retrieving becomes a bit complicated, but once we'll create a view for it, that problem is solved.
- Add a history table and a future table, both with only the data that we need to save history for. The current state will be retrieved as it is now.
This means having a daily job to move future records to current table when they become relevant, and current records to history when they are updated (except for when they are updated because the user made some mistake, in which case we just override the record).
Here are the main factors we think are relevant to making the decision:
- Retrieving current data using the first alternative takes about twice as the second alternative (and retrieving current data is 95% of retrievals performed).
- Creating a job and some mechanism to move records from current to history is a relatively big complication required in the second alternative, but avoided in the first. It might become a significant source for bugs.
- There's a big difference in the time it will take to implement the two alternatives, but considering the impact of the decision, it's negligible.
...And that's where we're stuck.. The first factor is a big plus for the second alternative, and the second a big minus.
From your experience which way is better? Why?