|
|
Posted by WombatDeath on 03/30/07 15:51
On Mar 30, 3:42 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> WombatDe...@gmail.com wrote:
> > I want my application to audit any data changes (update, insert,
> > delete) made by the users. Rather than have an audit table mirroring
> > each user table, I'd prefer to have a generic structure which can log
> > anything. This is what I've come up with:
>
> > TABLE: audit_record
> > *audit_record_id (uniqueidentifier, auto-assign, PK) - unique
> > idenfiier of the audit record
> > table_name (varchar) - name of the table where the action (insert/
> > update/delete) was made
> > pk_value (varchar) - primary key of the changed record. If the PK
> > itself has changed, this will store the old value.
> > user_id (varchar) - user who changed the record
> > date (datetime) - date/time at which the change was made
> > action (int) - 0, 1 or 2 (insert, update, delete)
>
> > TABLE: audit_column
> > *audit_record_id (uniqueidentifier, composite PK) - FK to
> > cdb_audit_record table
> > *column_name (varchar, composite PK) - name of the column with changed
> > data
> > new_value (text?) - value after the change
>
> > So every column which changes has its new value logged individually in
> > the audit_column table. However, I'm not sure what data type the
> > new_value column should have. The obvious answer (to me) is text, as
> > that can handle any necessary data type with the appropriate
> > conversion (we don't store any binary data). However, this table is
> > going to grow to millions of records and I'm not sure what the
> > performance implications of a text column will be, particularly given
> > that the actual data stored in it will almost always be tiny.
>
> > Any thoughts/recommendations/criticism would be greatly appreciated.
>
> Do you actually have anything (or any reasonable prospect of having
> anything in future) for which NVARCHAR(4000) wouldn't be good enough?
>
> Whatever you do, I strongly recommend keeping tabs on how quickly it
> grows, showing that trend information to the client, and (1) narrow it
> down to the tables that really need an audit trail and/or (2) come up
> with a sane archive-and-purge schedule.
Yeah, unfortunately we do have several tables with a column of type
text. These generally don't hold anything close to 4000 chars but
there's nothing actually preventing them from doing so. But...if
there's no tidier option I think I may just truncate to 4000 and be
done with it. We're not auditing to fulfil legal obligations or
anything nasty like that so I don't think it will be a problem.
Your point about maintenance is well taken. I've specified that the
application's auditing must be configurable on an entity-by-entity
basis, and every so often we'll archive away any old data for fast-
changing entities.
Thanks very much for your input!
Navigation:
[Reply to this message]
|