Database Design – Tracking Events in a Task Database

database-design

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

Best Answer

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.

Related Question