DEV Community 👩‍💻👨‍💻

Discussion on: Design a Table to Keep Historical Changes in Database

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.

encryptblockr profile image

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?