Storing Audit Storage

Question

For storing audit entries for auditing purposes, I've been wrestling with how to record what data has changed.

I've done two different ways in the past, but I'm creating a new system now and am trying to figure out which to use:

1. Have an AuditEntry and AuditEntryChange table. Each changed field value goes in the AuditEntryChange table and has a FK to AuditEntry.

2. Have the changed fields and values stored in XML in the AuditEntry table in a single field.

Which of the above would be more performant for saving and querying? (including the impact of serialization/deserialization if using XML). And which would take up less space?

Also, is best practice to use GUID or int PKs for audit tables? I can think of reasons for each.

Answer 1

Personally, for reporting it's a lot easier to have each field in a database.

GUID versus Integers depends on how many records you are going to have in the table. the integer takes 4 bytes versus 16 bytes for the GUID. If you are looking to have cross server deployment though GUIDs are a lot easier.

Here is a good article on the pro's versus con's.

Answer 2

The answer depends on how you plan to query the audit table(s). Things to consider about audit tables are that in a typical scenario the records are read-only and are inserted far more often than queried.

I would lean towards option 2 for the following reasons:

• Inserting a single row is faster than inserting multiple rows with foreign key constraints.
• Having an XML field gives a great deal of flexibility in how the audit data is structured without having the worry about database schema.
• SQL Server can query XML columns using XPath syntax so you can still have some of the relational querying capabilities.
• Selecting many records, such as for display in a form, is also faster, since there are no joins.
• This model can be easily ported no NoSQL storage.
• XML serialization would only be involved when inserting from code or loading back into code. You can still query the XML column directly via SQL.
• I would assume that space requirements would increase, though this depends on the size of the indexes that may result from a relational model.

As for int vs Guid for audit tables I would go with a Guid because:

• If inserting using an ORM like NHibernate there is no select after insert to retrieve the generated ID. You can insert in batches efficiently.
• While a guid is 4 times larger, for a million records that results in a difference of about 10 MB. Is that really an issue? Especially since it is not likely to query by the PK of an audit log.
• Porting to other databases or storage mechanisms is easier.