1

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?

Improve this question
27
  • 2
    It is also unclear to me how future records are related to the problem of historical change tracking. The only difference between a current record and a future one is an arbitrary datetime column with a particular value, which doesn't force you to treat these differently in terms of change tracking unless you choose to do so (which makes no sense, if the same solution works for both). Unless I'm missing something, the future records are not relevant to the problem of change tracking and/or are trivially avoided using a basic datetime filter even if relevant.
    – Flater
    Aug 10, 2021 at 9:08
  • 1
    @Flater - We can theoretically move away from SQL, but we find solving everything in SQL simpler. We create a view for retrieving the data once - and we can get it everywhere. As for updates and inserts - since we perform them from code and from SQL, we might need to implement them twice, but we generally prefer to perform everything once, so SQL seems better. Unless I'm missing out on something
    – Oren A
    Aug 10, 2021 at 9:38
  • 1
    @Flater - I mentioned the need for future records because it complicates a bit retrieving from a single table (you'll need the max date which is earlier than today), and it makes more sense to have another table for it in the second alternative than to have a "not present table". But I agree that it doesn't have an impact on the bottom line.
    – Oren A
    Aug 10, 2021 at 9:41
  • 2
    @Flater - I would think the fact we have SQL SP that retrieves/inserts/updates data means if we want it to be written once is should be using SQL, as using c# code from SQL is not best practice to say the least (if possible at all).. It's also more convenient to a developer or system analyst who's trying to understand what's the status of a certain entity, or solve a bug, to just run a view rather than debug.. I really don't see why we would want to implement that logic in code.
    – Oren A
    Aug 10, 2021 at 14:22
  • 1
    It looks a but weired that retrieving current data using the first alternative will slow down your system that much. Do you have added proper indexes for fast retrieval of "current" data? How are "current records" distinguished from historical or future records?
    – Doc Brown
    Aug 11, 2021 at 14:19

1 Answer 1

Reset to default
2
+100

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.
Improve this answer
2
  • Simplicity is subjective here. Mixing up three different things in the same table leads to give three different meanings and explanations every single time the table is involved. That transalted into code lead to a lot of boiled plate code and unecessary if/else blocks in places where they aren't supposed to be. Not mentioning the burden of transfering this knowledge, easly, among developers. Technically, over the paper, seeme simpler, but in practice could be not. Hard to say with so little knowledge of the applocation and the team in charge.
    – Laiv
    Aug 15, 2021 at 10:28
  • @Laiv Simplicity is quite objective, as applied to the context of the Question. "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).". I go with the information I have, and your imagined complexity is quite unlikely given that the original post describes use of a View.
    – Kind Contributor
    Aug 16, 2021 at 4:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.