SQL - Audit log table design - Which would you prefer?

For a project that the company I'm working on are setting up, we are in need of auditing (storing logs) for various of the tasks (changes made to tables in the system) that users perform. Currently, there are only three different types of tasks. But this may grow in the future.

My suggestion for this was the following schema table's and relationships (example):

Table AuditLog
------------------------------
Id | PK
Description
Created

And for every task:

Table ExampleTaskAuditLog
------------------------------
ExampleTaskId | FK PK
AuditLogId | FK PK

And:

Table AnotherExampleTaskAuditLog
------------------------------
AnotherExampleTaskId | FK PK
AuditLogId | FK PK

Basically, for every kind of task we need to audit, we would have a new table holding the relationship.

What another developer suggested, was the following:

Table AuditLog
------------------------------
Id (PK)
Description
Created
ExampleTaskId | NULLABLE
AnotherExampleTaskId | NULLABLE
Type | (an integer id which indicates whether this is a "example task" or a "another example task").

Basically, if we were to create a log for "ExampleTask", we would set the ExampleTaskId-field to the identity of the example task and the Type to the corresponding ExampleTask-enum value.

He suggested the above table because he is arguing about integrity(which I think is good!) and performance. Mostly because there are FK constraints, and one would need to join a table in order to get the relevant logs (yes, this is a RMDBS - MSSQL). Also, since there are two tables for every log, there would also need to be two inserts(integrity lookups etc). Sure, this is correct. But I can't see the issue. Especially not with performance since it's minimal. Also, the logs that are going to be stored are most likely not going to be more than 5-10K i开发者_如何学JAVAn total for the first year. In a couple of years, the tables might contain about 30-40K rows, at max.

What are your opinion on the above? Also, which one of the solutions above would you prefer, and why?

---

I'm not sure that I completely understand - if ExampleTaskId and AnotherExampleTaskId are the same data type, why not just have one table with the following columns?

• Id
• Description
• Created
• TaskId
• TaskType

That aside, your AuditLog table definitely should have a TaskType field, as otherwise it becomes relatively difficult to work out what type of change a record in the log represents.

Also, I would avoid (where possible) denormalizing your tables (i.e. having columns that will always be null for a given task type) unless absolutely necessary (for performance reasons for example). Instead I'd recommend using tables and joins for the task specific columns:

Table ExampleTaskAuditLog
------------------------------
AuditId (PK)
TaskSpecificField
AnotherTaskSpecificField

---

Assuming you are logging events/tasks/actions, and not modifications to individual tables, I'd go with the first, for the same reasons @Kragen says (upvoted). But if you're auditing changes to incividual tables, I'd go with one audit table for each table being audited.

A side-question, do you really want those foreign keys? If an item (row) in a table is deleted, you would then have to delete the audit trail for that item. Do you only need audit logs for existing items? ("Audit" generally implies "watch what they're up to", and if "they" can wipe out all trace of their activities that's generally frowned upon, even on Wall Street.)