What would be a suitable way to log changes within a database

Question

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 for example news_audit table which would replicate all the columns in the news table and 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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

There are a few things to take into account before you decide which solution to use:

If your table is large (or could become large) your audit trail needs to be in a seperate table as you describe or performance will suffer.

If you need an audit that can't (potentially) be modified except to add new entries it needs to have INSERT permissions only for the application (and to be cast iron needs to be on a dedicated logging server...)

I would avoid creating audit records in the same table as it might be confusing to another developer (who might no realize they need to filter out the old ones without dates) and will clutter the table with audit rows, which will force the db to cache more disk blocks than it needs to (== performance cost). Also to index this properly might be a problem if your db does not index NULLS. Querying for the most recent version will involve a sub-query if you choose to time stamp them all.

The cleanest way to solve this, if your database supports it, is to create an UPDATE TRIGGER on your news table that copies the old values to a seperate audit table which needs only INSERT permissions). This way the logic is built into the database, and so your applications need not be concerned with it, they just UPDATE the data and the db takes care of keeping the change log. The body of the trigger will just be an INSERT statement, so if you haven't written one before it should not take long to do.

If I knew which db you are using I might be able to post an example...

Answer 5

What we do (and you would want to set up archiving beforehand depending on size and use), but we created an audit table that stores user information, time, and then the changes in XML with the table name.

If you are in SQL2005+ you can then easily search the XML for changes if needed.

We then added triggers to our table to catch what we wanted to audit (inserts, deletes, updates...)

Then with simple serialization we are able to restore and replicate changes.