|
Posted by Ed Murphy on 03/30/07 14:42
WombatDeath@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.
Navigation:
[Reply to this message]
|