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.