Storing Changes To Records

Everything I am talking about relate to relational database, specific MySQL.

I have a number of tables in a database and for a moderate number of them, I am going to want to store a history of the records values when it changes. I have seen this done in a couple of different ways:

• One Table/One Field - Basically there is one table that store the history of all the table that need history storage. All change are recorded in one field as a text data type.

• Table Per Table/One Field - Same as above except the each table has its own history table (ie. Projects/ProjectsHistory, Issues/IssuesHistory, etc...).

• Table Per Table/Field Per Field - This is like the above in the each table has it own histroy table but also the history table has pretty much the same definition as the regular table with an additional of additional history related fields (updateDatetime, updateUserId, etc...).

What are some of the advantages and disadvantages to these different methods of storing record history? Are there other methods that I have not thought of?

Since you've got many tables with varying numbers of columns, #1 would be out since you'd have a massive table with the aggregate of all your columns, and lots of nulls.

Between #2 & #3 I think you have a decision to make regarding the design complexity you want to manage. My view is that it would be easier to maintain an exact archive replica for a given table, and store the whole rowstate (with modified time). Think of a case where you update more than one column of a row. In that case #2 would log an entry for the change of the columns separately, even though it was the same transaction. I'd go w #3 for reducing complexity, and capturing point in time row state.

StackExchange Database Administrators Q#15671, answer score: 3