I worked in several companies and in each of them audit tables have been storing full snapshots of records for every change.
To my understanding it’s enough to store only changed columns to recreate record in any given point of time. It will obviously reduce storage space. Moreover I suppose it would improve performance as we would need to write much smaller amount of data.
As I’ve seen it in across different databases and frameworks, I’m not putting any specific tag here.
I’d gladly understand reasoning behind this approach.
Advertisement
Answer
Here are some important reasons.
First, storage is becoming cheaper and cheaper. So there is little financial benefit in reducing the number of records or their size.
Second, the “context” around a change can be very helpful. Reconstructing records as they look when the change occurs can be tricky.
Third, the logic to detect changes is tricker than it seems. This is particularly true when you have NULL values. If there is a bug in the code, then you lose the archive. Entire records are less error-prone.
Fourth, remember that (2) and (3) need to be implemented for every table being archived, further introducing the possibility of error.
I might summarize this as saying that storing the entire record uses fewer lines of code. Fewer lines of code are easier to maintain and less error-prone. And those savings outweigh the benefits of reducing the size of the archive.