Implementing a simple audit system

[NotionCommotion]

I have found that implementing audit trails gets complicated and big quickly.

 

I would like to take a simpler approach for a PHP application. Instead of logging content before and after the change, I would like to only log who and when a change was made to a given record in a table, and not the before or after values or even what columns were changed.

 

My thoughts are something like the following:

1. Use PHP to store the logged on users ID in a MySQL session.
2. Set up a trigger whenever any column in a given table is changed, and store the table name, PK of the modified/deleted record, and timestamp in another table called audits.

Ideally, I wouldn't store the actual table name as this would make it more difficult to ever changing table names (which hopefully is never necessary). Is there a systems MySQL table (i.e. used by the MySQL database) which could be used?

 

In regards to storing the PK of the added/modified record (deletes will just be tagged as deleted) in the audits table, the only way I think I can use the DB to enforce foreign key integrity is creating a bunch of of one-to-one tables between audits.id and the audited table, and will probably just use the application to enforce.

 

I would appreciate comments whether this approach is feasible and any recommended changes.

 

Thank you

Edited August 6, 2015 by NotionCommotion

[scootstah]

I've been using Symfony2 with Doctrine for a long while now, so I will tell you how I accomplish this with that.

 

Firstly, everything is an object in Symfony2. Using Doctrine ORM, all of the database tables are mapped to model classes/objects. Then, in a log table, it would store the primary key of the changed record, the primary key of the user who made the change, a timestamp, a serialized chunk of data changed, what operation was used (create, update, delete, etc), and the object class that the change was made on.

 

So, you're on the right track. I would argue that it's good to store the actual change set, that way you know what actually changed. This can be important in some situations, and it also makes it possible to revert data back to the previous state.

 

You don't necessarily need to maintain foreign key integrity in the audit table. Because if a record was deleted, the ID that it points to will be gone anyway. You don't want your audit log to be erased if an object is deleted.

[NotionCommotion]

Thanks scootstah,

 

I've considered embracing something like Symfony2 for a while, but have not yet jumped in.  Maybe some day soon?

 

I agree it is good to store the actual change set if one is serious about audits.  I am on the fence whether I even do anything, thought maybe something very simple is better than nothing.

 

Good point about foreign key integrity.