Saving change history using PHP and MySQL

Question

I have a couple of tables in which I want to save the change history. What is the best way to do this? It will be several fields (about 20).

Should I create a duplicate table and just send old data to it? Should I use triggers? Or do I need to create a separate table and just keep track of the changes made?

Answer 1

This is the question I asked a few months ago, but I would do it again.

History of database records changes

Answer 2

We are very pleased with our choice, which uses two tables for an object with a version.

The tables look something like this:

Person table:

• id (PK)
• version (counter for optimistic locking)
• current (foreign key person_version )
• ... (any property that does not change)

person_version table:

• id (PK)
• person (not null) (foreign key reference person )
• timestamp (used for sorting)
• ... (any property that may change)

Since records in person_version will never change, they are easy to cache (as long as there are no references to tables that may change)

Answer 3

Some ORMs, such as Propel , can “handle” this frequent need “initially”.

Check the behavior of the version .

• It automatically adds the version column to the version table (for example, mytable ) and creates the table mytable_version with the necessary columns containing mytable_id and version ).

• It provides a simple API for querying table versions. For example, when you do $myobject->save(); , it automatically populates the mytable_version table and updates the mytable.version field accordingly.

Answer 4

You can simply add another column to your table that keeps track of the record ID, while the active PK is the revision ID. The one with the highest revision identifier is the last and active record, other records with the same input identifier are previous versions.