Design a Table to Keep Historical Changes in Database

A classical problem: how to design a table so that it can keep historical changes in database?

Photo by Fredy Jacob on Unsplash
Demands

1. Use Effective From and Effective To Fields

I DON’T like this approach but I’ve seen people practicing it, struggling with it but still insisting to do it. Hence, I would like to demonstrate why it’s a bad approach doing so.

Demands

2. Use a History Table

What if I want to keep a whole record as a history but doesn’t want to affect my actual table? Then you may want to create a history table for Demand, which has the same fields as Demand table itself.

DemandHistory

3. Use an Audit Table

A better solution is to create an audit table to record every single change in every field, which saves the spaces by eliminating redundant information. The table looks like this:

DemandAudit

Conclusion

In this article, I talked about 3 approaches to keep historical changes in database, which are effective from and effective to fields, history table and audit table.

Audit Table

Pros:

  • Not affecting actual table
  • No Redundant information
  • Suitable for: actual table has many fields, but often only a few fields are changes

History Table

Pros:

  • Simple query to get the complete history
  • Not affecting actual table
  • Suitable for: A lot of fields are changed in one time
  • Suitable for: Generating a change report with full record history is needed.
Photo by Paul Gaudriault on Unsplash

Developer @AE | Fullstack Dev