Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Are you looking for tools to track changes, or the database architecture to store them? In the first case, I think you'll have to decide on a database platform first, because most of the solutions are specific to a particular database server (sometimes even version/edition of it). –  Daniel Hutmacher Aug 28 at 13:37
    
I'm looking for a way to design the database architecture so that the changes can be tracked and reported on effectively. Not a tool, no. –  cskilbeck Aug 28 at 13:45

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
I like this, it allows multiple field changes in a single 'event' which is cool, and I'm not too concerned about the storage. Thanks! –  cskilbeck Aug 28 at 16:15
    
An optimization might be to set unchanged fields to null and walk back the chain to get the current value, although that would involve more than a simple query to get a task. Not sure it's workable. –  cskilbeck Aug 28 at 16:44

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.

share|improve this answer
    
Sorry, the question was missing a couple of fields from the Events table (UserID and TimeStamp). The idea is that the events table is the audit. I'm just unsure about how to specify what fields changed and how to store the old/new values efficiently. –  cskilbeck Aug 28 at 16:17
    
It sounds like Events table is an audit of physical events, but you're also auditing that table itself when you track changes made to it. You can store old values before they are changed using a trigger on UPDATE. This is essentially the same as @blobble's answer except it uses a separate table. –  dartonw Aug 28 at 16:27
    
With that scheme, I wouldn't even need an Events table though, right? –  cskilbeck Aug 28 at 16:41

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)
share|improve this answer
    
This was my original thought, but what about BLOB and TEXT fields? –  cskilbeck Aug 29 at 8:22
    
You don't specify which DBMS you're using, but from the context of MS-SQL (what I know best), you shouldn't be using the TEXT datatype; it's deprecated in favor of varchar(max), which works just fine in this scenario. As far as BLOBs go, I admit I don't have a great answer for you: In the times I've had to deal with BLOBs, I generally try not to have them stored in the database proper; I prefer to keep the BLOB on the filesystem, and have the path to that file (a varchar or nvarchar) stored in the table. –  Adrien Aug 29 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.