DEV Community

Cover image for Design a Table to Keep Historical Changes in Database

Design a Table to Keep Historical Changes in Database

Zhiyue Yi on January 16, 2020

Visit my Blog for the original post: Design a Table to Keep Historical Changes in Database Back to months ago, I encountered a classical problem: ...
Collapse
 
tidunguyen profile image
TiDu Nguyen

I think History Table is the best and most popular way to do this. Audit table has 2 huge drawbacks:

  1. Unscalable because 1 field = 1 new row. For highly interactive data, this can bring serious performance issues. Although History Table takes up storage space, it will rarely have such a significant impact on query speed. Most systems would trade storage space for query speed. Also, we DON'T usually need to store every fields of the original record in the History Table, store things that matter.
  2. Use a foreign key to link to original table for history is actually a very bad idea because the other fields in the original table are very much likely not the same as when the change was recorded, even deleted like Wilson Liu mentioned. This is crucial for records where the meaning of 1 field is dependent on other fields. In those cases, the repeated data from History table are not "redundant" at all.

I want to add more that for an ordinary system where real-time data is not crucial, table history can be recorded periodically using a cron job (aka. scheduled task), record history per transaction is usually costly without much benefits.

Collapse
 
encryptblockr profile image
encryptblockr

how do we then insert records into the original table and history table at same time? do we run insert query to insert into the 2 tables at same time? since you said using foreign keys is a bad idea...can you please share details of how to approach this?

Collapse
 
syedalisait profile image
Syedalisait

Hi - Very good post on Auditing Mysql.

Question:
Can you actually show an example of how would you write a trigger for 3rd approach which is the audit table. I have implemented this but the trigger query that I use is very costly. So wanted to understand how would you implement column level changes to be recorded in audit table

Collapse
 
zhiyueyi profile image
Zhiyue Yi

Hi, thanks a lot!

I used C# and entity framework and the audit records are generated at code level. So unfortunately I didn't write a trigger for that.

Collapse
 
nessuarez profile image
Nestor Suarez Alfonso

What if your entity is stored serialized in a XML column? What strategy you think is best for that case?

Would be interesting having a History table storing the previous serialized value? What if we use a diff algorithm and store diff changes to reduce redundancy? In our case, when you get the history of an entity, you get all changes (maybe 10-20 changes records)

Collapse
 
sardello profile image
mpisau

A history table sounds like a simple solution to me. But I still see a few cons:

If I have a lot of tables, I also need a lot of historical counterparts.

And if the database is still in development or fields change, then the fields of the historical table must also be adjusted and scripts must be adapted.

So I now had the following idea for a single historical table:
ID, TableName, RecordID, JSON (with dataset dump from Table).

Would this solution have any disadvantages?

Collapse
 
arendpeter profile image
Arend Peter Castelein

Great post this was very helpful!

To clarify, if I want to track the history for N tables, it sounds like the history pattern would require N duplicate tables, but the auditor pattern would only require 1. Is that correct?

Also, what are your thoughts on having a history column? Here you add a "history" column to your table with a JSON blob of that history for that row. We don't expect many changes to the data, so we don't expect the blob size to be a problem

Collapse
 
geethakrishnach profile image
geethaKrishna

When I saw this I thought it will solve the N tables problem. But investigating further this is what i found...

The history column will only point to one history record, but to get a full history of the a single record we will need to recursively call each history record right? That could become very complicated in comparison to having different history tables.

Collapse
 
wilsonliu4 profile image
WilsonLiu

How to manage history table if I want to delete a record from the main table?
Thanks

Collapse
 
anandpowar profile image
Anand Powar

One option is to use a soft-delete i.e. set IsEnabled = false

Collapse
 
encryptblockr profile image
encryptblockr

how do we then insert records into the original table and history table at same time? do we run insert query to insert into the 2 tables at same time?

Thread Thread
 
anandpowar profile image
Anand Powar

You can run two distinctive inserts within the same transaction scope.


Begin transaction 
  Insert into original-table
  Get inserted auto-id (optional)
  Insert into history-table
Commit transaction

Enter fullscreen mode Exit fullscreen mode