Hi,

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.

Thanks
Alex

Sorry for replying to myself - I forgot to state that I'm using SQL
Server 2000 standard edition.

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.

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!

(WombatDeath@gmail.com) writes:
> 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.

That is not going to be fun in SQL 2000. In SQL 2005 you could build a
generic audit solution on the xml data type.

I would recommend that you research the market for audit products. I
know for instance that ApexSQL has a something they call SQLAudit
if memory serves.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

>> 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. <<

Any chance you might post DDL instead of your personal pseudo-code?
And I hope you know that auto-numbering is not a relational key.
Finally, Google "EAV design flaw" for tens of thousands of words on
why this approach stinks. There is no such magical shape shifting
table in RDBMS. Data Versus metadata, etc.? Freshman database
course, 3rd week of the quarter?

While you might like this kludge your accountants and auditors will
not. NEVER keep audit trails on the same database or even the same
hardware as the database.

>> Any thoughts/recommendations/criticism would be greatly appreciated. <<

Look at third party tools that follow the law and get a basic dat
modeling book.