• 0

Compare Values for Audit Trail


Question

I'm attempting to develop an audit trail/tracking solution for an existing database written in PLSQL/PHP - however I'm still unsure as of yet on an easy (to implement and maintain) solution for tracking changes to fields/values. For instance, the project tracking portion of the DB APP tracks over 200 fields and ideally I'd like a nice way to show a history of changes, such as:

5/10/2010 - Project 435232 updated by John Doe
Changed Project Name (Old: Test Project; New: Super Test Project)
Changed Submission Date (Old: 5/10/2010; New: 5/11/2010)
Changed Description (Old: This is an example!; New: This is a test example)

Essentially for each field (db column) it would output a new line to show the old/new values. So far my current idea is saving the current version of the data to a temporary table, updating the primary table with the new data then loading each row into an array and doing an array compare to determine the differences. This seems a bit convoluted, and if there is an easier method I'd love to know it.

Any ideas or suggestions are much appreciated!

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

I've never done anything like this, but I would suggest looking at a wiki's source code. Wiki's do exactly what you are talking about doing, so you may glean some useful insight into how you could approach it from them.

Link to comment
Share on other sites

  • 0

Wikis typically store the entire page content for each revision, not a diff of the changes as people might expect. It leads to a larger database, but much faster performing site.

I would suggest employing a similar tactic, create a history table containing all the historic and current rows primary-keyed on the id and versionNumber. Keep this separate from your main table where you will only store the latest revision.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.