I like the simplicity of this approach. Makes me think I might be designing myself to hell with my somewhat convoluted multi-table monstrosity :).
Cheers, - hugi > On 12. ágú. 2015, at 15:05, Mike Kienenberger <[email protected]> wrote: > > On Tue, Aug 11, 2015 at 8:49 PM, Aristedes Maniatis <[email protected]> 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.
