Php – Keeping a revision history with PHP and MySQL

MySQLPHP

I have a couple tables that I want to keep a revision history on. What is the best way to accomplish this? It's going to be several fields (20 or so).

Should I create a duplicate table and just submit the old data to that? Should I use triggers? Or should I create a separate table and just track the changes made?

Best Answer

We're pretty happy with our choice which is using two tables per versioned entity.

Tables would look similar to this:

Table person:

  • id (PK)
  • version (counter for optimistic locking)
  • current (foreign key referencing person_version)
  • ... (any property that won't change)

Table person_version:

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

As entries in person_version won't ever change, it's easy to cache them (as long as there aren't any references to tables that might change)