Recording history - All changes in one table, or three tables?

Question

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:

1. In some scenarios the users (-admins) can change the history.
2. 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).
3. Saving the data is performed from several modules (different services - some code and some SQL)
4. We will need to record history for many other data points in the future.

So here are the two alternatives we came up with:

1. 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.
2. 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:

1. Retrieving current data using the first alternative takes about twice as the second alternative (and retrieving current data is 95% of retrievals performed).
2. 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.
3. 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?

Answer 1

From your experience which way is better? Why?

A single table

Because:

• Simplicity is more important than performance. Start with simple, then optimize performance. (Simple = less work, less complexity => less maintenance, fewer bugs, easier to understand by future programmers)
• You have one table structure, so start with one table. That's simple.
• It seems like you have overlooked a range of simple performance improvement options.

Options for performance enhancing the simple solution:

• Indexing - as others have suggested
• View Optimisation - review query plan because you can improve the structure of the SQL itself too
• Table Partitioning - Use the Database Tools, don't do it yourself.
• Materialized View - Use the Database Tools, don't do it yourself.
• Secondary Table - Like a Materialized View, but instead you manually build the materialized table with a batch process.

Remember, for all performance optimisation: measure then improve.

---

Here are some reasons why you might consider "3 tables":

• Read/Write duty - both i) separate process groups reading/writing two history vs future; and, ii) at high volume. Separate tables should result in less lock contention. But this should be considered a progression of performance optimisation, after you work through the simpler measures first - start with Simple.
• Security Partitioning - where data access roles need to be enforced i) in the database; and, ii) at the table level, differently for history vs future. In your situation, you should be doing that with VIEWs, not at the table level.
• Separate Databases - where the History table is in one SQL Server instance, and the Future table is on another SQL Server instance. Which doesn't seem to be the case here.