Designing a action history database for different types of actions

Question

In a bugtrackingsystem, I want to keep track of some actions. Actions like change status, add comment, change priority, and other actions.

The problem is, how do I store those different actions, with different parameters. For example when someone changes status, it needs to save statuschange, with old status and new status. But when someone adds a comment, it needs to save comment added, with comment id.

One solution could be to save those parameters as plain text. Like "oldstatus => new status", "comment 001 added". But that doesn't seem really viable.

Does someone know how this could work best?

Answer 1

You can store the actual status in the bug table, and keep a history of status changes in a different table:

changeid (pk) - ticketid (fk) - date - oldstatus - newstatus - username

Comments usually go in a different table too:

commentid (pk) - ticketid (fk) - date - username - comment

When retrieving data, you can retrieve all comments for a bug like:

select 
from comments 
where comments.bugid = 12

P.S. (pk) = primary key, (fk) is foreign key

Answer 2

I'd prefer keeping a separate entry in TaskInfoRevision table for each change, with all the fields like status, comment, properties, etc., and then determine what has changed in your application code, not in the DB itself.