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