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?