Should I include primary key for Audit Table in SQL?

Question

I am creating an audit table for tracking changes done on a record in main table.

Here audit table is the exact duplicate of main table (say Employee Table) but will only have 'inserts' for every changes happens in the main table. So it will have duplicates (same EmployeeIDs), so should I add separate Audit_ID for each entry?

Answer 1

Sure you need some kind of a primary key in the table.

It will be easier to remove duplicates and to track the 'reason of insert' (for debugging purposes) if you have one.

Some RDBMS's (like MySQL with InnoDB) actually create a hidden primary key for you if you didn't do it explicitly, so just do it yourself and make if visible and usable.

Answer 2

You could use the employee id and the audit datetime as a composite key...

Answer 3

Yes. I believe a non-semantic primary key in every table is a best practice, since it allows you to reference it in a pragmatic way. You can visualize "the changes from audit-id to audit-id+1" far more easily than using dates.

Answer 4

You pretty much have to, as while the EmployeeID in Employee will be unique (an update will not add a new row, just modify the existing row), the audit table will have multiple rows with the same EmployeeId (one for the initial insert, one for each subsequent update, of that employee).

Besides, the entities in Employee are employees. But the entities in audit are audit records. They shoulld have their own id.

You'll want this if ever something goes wrong and you need to actually update or delete an audit record. Indeed, if an employee is inserted, then one column's value is updated, then updated again to be the original value, you now have two identical records in audit. Which would have to be deleted or updated together (unless you used a limit clause in your update or delete.) Messy.

Which also points up the usefulness of adding a timestamp to the audit table too. But don't think you should use that and employeeid as a composite key. First, composite keys suck. Second, it's entirely possible a timestamp's granularity is less than the time it would take your system to perform two updates (two updates of the same employee, as in, say, a batch operation). (A Sybase datetime has a three millisecond granularity; an Intel Core 2 Extreme can do nearly 200 million instructions in that time.)