Tracking events in a task database 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 -&gt; users
Owner int -&gt; users
Status int -&gt; statuses
Priority int -&gt; 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).
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.