What Would be a Suitable Way to Log Changes Within a Database Using CodeIgniter

By : Malachi
Source: Stackoverflow.com

I want to create a simple auditing system for my small CodeIgniter application. Such that it would take a snapshot of a table entry before the entry has been edited. One way I could think of would be to create a news_audit table, which would replicate all the columns in the news table. It would also create a new record for each change with the added column of date added. What are your views, and opinions of building such functionality into a PHP web application?

By : Malachi


What scale are we looking at here? On average, are entries going to be edited often or infrequently?

Depending on how many edits you expect for the average item, it might make more sense to store diff's of large blocks of data as opposed to a full copy of the data.

By : Amber

One way I like is to put it into the table itself. You would simply add a 'valid_until' column. When you "edit" a row, you simply make a copy of it and stamp the 'valid_until' field on the old row. The valid rows are the ones without 'valid_until' set. In short, you make it copy-on-write. Don't forget to make your primary keys a combination of the original primary key and the valid_until field. Also set up constraints or triggers to make sure that for each ID there can be only one row that does not have it's valid_until set.

This has upsides and downsides. The upside is less tables. The downside is far more rows in your tables. I would recommend this structure if you often need to access old data. By simply adding a simple WHERE to your queries you can query the state of a table at a previous date/time.

If you only need to access your old data occasionally then I would not recommend this though.

You can take this all the way to the extreme by building a Temportal database.

In small to medium size project I use the following set of rules:

  1. All code is stored under Revision Control System (i.e. Subversion)
  2. There is a directory for SQL patches in source code (i.e. patches/)
  3. All files in this directory start with serial number followed by short description (i.e. 086_added_login_unique_constraint.sql)
  4. All changes to DB schema must be recorded as separate files. No file can be changed after it's checked in to version control system. All bugs must be fixed by issuing another patch. It is important to stick closely to this rule.
  5. Small script remembers serial number of last executed patch in local environment and runs subsequent patches when needed.

This way you can guarantee, that you can recreate your DB schema easily without the need of importing whole data dump. Creating such patches is no brainer. Just run command in console/UI/web frontend and copy-paste it into patch if successful. Then just add it to repo and commit changes.

This approach scales reasonably well. Worked for PHP/PostgreSQL project consisting of 1300 classes and 200 tables/views.

This video can help you solving your question :)
By: admin