On Tue, Aug 11, 2015 at 8:49 PM, Aristedes Maniatis <a...@maniatis.org> wrote: > I'm interested in a different part of the problem you are solving. Other than > the relationship data discuss here, how are you storing the actual changes in > your audit table? Does your problem just require "Bob changed record 23" or > are you keeping a complete "diff" of the changes? If the latter, how are you > serialising those changes into the audit log?
I've been involved with auditing in at least three projects over the last 15 years, and have handled them similarly each time, although we also have used record-level logging (adding date/time of last change to each record) in addition in one project. This is a combined summary of what I've used in the past and what I'd probably use in the next project, along with any particular application-specific fields, such as REAL_USER_ID and EFFECTIVE_USER_ID. <db-entity name="LOG"> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="22"/> <db-attribute name="MODIFICATION_DATE" type="TIMESTAMP" isMandatory="true"/> <db-attribute name="MODIFICATION_TYPE" type="VARCHAR" length="1"/> <db-attribute name="TABLE_NAME" type="VARCHAR" isMandatory="true" length="32"/> <db-attribute name="COLUMN_NAME" type="VARCHAR" isMandatory="true" length="64"/> <db-attribute name="FOREIGN_RECORD_KEY" type="INTEGER" length="22"/> <db-attribute name="FKEY_CONDITION" type="VARCHAR" length="200"/> <db-attribute name="OLD_VALUE" type="VARCHAR" length="4000"/> <db-attribute name="NEW_VALUE" type="VARCHAR" length="4000"/> <db-attribute name="EFFECTIVE_USER_ID" type="INTEGER" length="22"/> <db-attribute name="REAL_USER_ID" type="INTEGER" length="22"/> </db-entity> It's a complete diff of any database change, when it was made, and who made it. If necessary, it can be used to reverse a change or replay a change back. Not only does it fulfil general auditing purposes, but it's been very helpful in debugging what an end-user really did as opposed to what they claim they did. It could potentially be hard to determine what data was committed together since it's at such a fine-grained level and the application is multi-threaded. Perhaps adding a commit id would be a slight improvement, but the MODIFICATION_DATE timestamp has been sufficient so far. For a schema where every record has a single primary key of a specific type (such as integer), you only need FOREIGN_RECORD_KEY. If you have compound primary keys, then you need to also use FKEY_CONDITION which is a string-representation of your primary key.