Audit trails and entity relationships

Question

I'm working on an order system and implementing an audit log. Two main concerns are:

1) While auditing a line item , you should only see audits for the line item

2) While auditing an order, you should see audits pertaining to the header information AND line item.

For example, a line item would have an audit trail for what was changed to the line item fields, in addition any adding/removing/modification to comments, photos attached , etc would be also be logged.

An audit for a order would contain all the things above, plus changes to the actual order (shipping location changes, purchaser name changes, etc).

Instead of mirroring the relationships for the audit trails tables, I am planning on having only 2 tables, which are as followed

Audit_Log:
    -audit log id
    -entity type
    -entity id
    -audit timestamp
    -audit user
    -audit message (serialization of object changes most likely)

Audit_Relationship
    -audit log id 1
    -audit log id 2
    -is child of entity

This will greatly simply queries and sorting of audit trails. Big plus is all the audit trails is in one table. You can just sort by timestamp and get an idea of what exactly is going on in the system (although, i think audits are meant mainly to focus on a smaller area , ie a specific order, to see changes).

Has anyone tried a similar design? I'm concerned if this confuses entity relationships at all. (Ie, a comment audit long for a line item will also be attached to an order, even though that relationship doesn't exist in the business logic.) The "is child of entity" was used to make this a bit more clear...

*I am using php with Doctrine 2, but I think this is an abstract enough idea to work with any ORM.

**(ADDED) I am also using MySQL, and considering on using database triggers rather then doing this at the ORM library level.

**(ADDED) Using the method described above might be a terrible idea. When attaching a audit trail, we need to get all the entities that will be associated with the audit trail. In order to do so we would need to implement a method to get a list of effected id's. Maybe some special annotations Doctrine uses to figure which properties should be followed when the audit log is "bubbling up" and attaching?

Anyways, shifting to longer read times using joins sound much more reasonable then longer write time for this feature.

Answer 1

Never never never have only one table for all auditing. This becomes a hot spot in the database and is a guarnatee of poor performance. You can do something simliar to that but do it for each audited table (We have script that will create new audit tables in our structure as we need them. )

I prefer to have the relasionship make the details subordinate to one thing at defines the actual audited information which is the auditlogid and date time. That way it is easier to relate all records together for one action (say you had a bad delete that took out 25000 records instead of 10, it is much easier to get them back from the audit tables if they are all related together. Make sure you think about how to get data out of the audit table to fix a mistake and write the script to do that.

Never perform auditing at any level except through triggers on the database. You want to catch all changes not just those made by one application. Any unauthorized changes made through a stealth method are even more important to capture even if you think the application is the only way to change the data. And I have never seen any database of any size that didn't have the need to run run ad hoc data changes outside the application.