CODEWITHSUNDEEP
phpmysql
Keith
Keith

Reputation: 1372

Keeping a revision history with PHP and MySQL

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?

Upvotes: 6

Views: 7100

Answers (4)

sfussenegger
sfussenegger

Reputation: 36115

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)

Upvotes: 5

Maxime Pacary
Maxime Pacary

Reputation: 23091

Some ORMs like Propel can "natively" handle this frequent need.

Check the versionable behavior.

  • It automatically adds a version column to the "versioned" table (say mytable), and creates a mytable_version table (certainly containing columns mytable_id and version).

  • It provides a simple API to query versioned tables. For example, When you do $myobject->save();, it automatically populates table mytable_version and updates field mytable.version accordingly.

Upvotes: 3

JF Dion
JF Dion

Reputation: 4054

Here is the question I asked a few months back, but I would do it the same way again

Database entries modification history

Upvotes: 2

matthiasmullie
matthiasmullie

Reputation: 2083

You could just add another column to your table which keeps track of the entry-id, while the active PK then is the revision-id. The one with the highest revision-id is the latest and active record, the other entries with the same entry-id are the previous revisions.

Upvotes: 0

Related Questions