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