Tracking events in a task database

Question

In designing a task database with the usual set of tables (Users, Tasks etc), I'm wondering if there is an accepted way to track changes to fields in the task table?

I could just provide a text log of what changed and when, but can I somehow store the old and new values in their natural type? I could have one field for each datatype (int, datetime etc) and then a bitmask to denote which one has relevant data in it, but it seems like it would be a bit clunky to report on. Another idea was to have one table for each datatype (IntEvents, DateTimeEvents etc) but that looks problematic too. Presumably this is a fairly common problem, has anyone else solved it in an elegant way? It needs to be compatible with SQL Server and MySQL at least, and Postgre as well ideally.

EG:

TABLE: Tasks
ID int pk
Name varchar(255)
Description text
Created datetime
DueBy datetime
Creator int -> users
Owner int -> users
Status int -> statuses
Priority int -> priorities
// etc

How can I create an events table to track changes to all these different datatypes?

The simple text log table could be:

TABLE: Events
ID int pk
UserID int
TimeStamp datetime
Fieldname varchar(32)
OldValue varchar(255)
NewValue varchar(255)

But this is a bit difficult to report on (eg show me all the events where a DueBy date was moved back by more than N days)

I've looked at table design to track significant events associated with an item but I'm not sure it's quite the same problem (unless I've misread it).

Answer 1

To me it would make sense to keep them in the same table, providing it isn't going to be an absolutely massive table. Change your table to include another field ParentTaskID which represents the original ID of the changed item:

TABLE: Tasks
ID int pk
ParentTaskID int
Name varchar(255)
Description text
Created datetime
DueBy datetime
Creator int -> users
Owner int -> users
Status int -> statuses
Priority int -> priorities

Then if the ParentID field is NULL, its the active record. If the ParentID field has a value in it, that value refers to the original tasks ID field.

The advantage of this is that there is one less join, if just pulling out all items for a task its simple:

SELECT * FROM Tasks
WHERE ID = 554 OR ParentTaskID = 554

Plus less management if you need to change/add any fields, create/rebuild indexes etc in the table.

If you are reporting you can have a conditional colour/indicator based on whether the field is an original (ParentTaskID IS NULL) or a modification. Your Created datetime field is then used to indicate the create date of each of the modification lines as well as the create date of the original task. In addition your Creator user ID could then link to the user who made the change.

Answer 2

You can create a copy of the original Events table, e.g. EventAudit, with an additional field for the date the change was made and perhaps a field identifying the user who made the change, and insert old values to that table. It needn't have a primary key (although it could): EventID would be a foreign key from Events.

This design would enable reporting on changes the same way the original tasks are reported, and you can index any number of columns in the table for that purpose. You may need to consider database size/disk space used by this table; if there are any BLOBs in Events you would probably want to insert them into EventAudit only if they have changed. Otherwise I suggest copying the entire row before the change.

Answer 3

I've seen the following pattern many times: a single audit table that's used for all audit-related activities. Roughly:

• Name of the table that was changed
• GUID of the record that was changed (that is, the GUID that identifies the changed record in its parent table)
• Name of the field that was changed
• Original value of the changed field (in the audit table, this is a nvarchar; most non-varchar values in our database can be safely coerced to and from in this way)
• New value for the changed field (nvarchar as above)
• Time the change happened (datetime)
• User ID under which the change was made
• Type of change (insert, update, delete)