History log on Ticket system database

I'm creating a Support Ticket system and it need to have a history of all changes made on that ticket. For example:

• Status change;

• Changed the responsible assistant;

• Sector/Department change;

• Etc..

This is the structure I have at the momment:

I was thinking about it and tought it may be very confusing, or at least not the best option, to create a table structure to store the respective type of change and the id from the correspondent table. For example:

Type of changes: 
1-> status change;
2-> Department change;
3-> Assistant change; 
[etc]

id  |  ticket_id  |  history_type  |  old_value  |  new_value  |  create_date
1   |      2      |       1        |      3      |      6      |  ....
2   |      7      |       3        |      5      |      2      |  ....

The other option I have, is to write plain text, instead of saving the Id's, for example:

id  |  ticket_id  |  message  |  create_date
1   |     1       |  Status change from 'Open' to 'Closed'  |  ....
2   |     2       |  Assistant change from 'John' to 'Kyle'  |  ....

Is there anything wrong with one or another? Or am I missing a important consideration on doing this type of history structure? Or what would be the best option?

I'd say that as long as you can guarantee that you (or anyone else) won't change the definitions of the '3','5','6','2' values somewhere down the road, it's not a bad idea.

You'll end up having to do a little more work with the first option to get it into a "User-friendly" view (if that's your end goal), but it seems like INT values (old_value, new_value) would be easier to store than full text values (message). The main advantage to your second option is that it's easier to read and you don't have to link a few tables together to get the user-friendly version.

Another way I've seen it done (Particularly by Salesforce) is to select which fields you'd like tracked in an object. Say "status" and "assistant" are your choices. Salesforce will have a structure as follows:

id | ModifiedDate | OldValues                     | New Values
1  | 08/26/2016   | Status: Open, Assistant: John | Status: Closed, Assistant: Kyle

This is if at least one value changed. So even if the assistant hadn't changed, it still displays all field values.

id | ModifiedDate | OldValues                     | New Values
  1  | 08/26/2016   | Status: Open, Assistant: John | Status: Open, Assistant: Kyle

It also tracks initial "inserts" as such:

id | ModifiedDate | OldValues             | New Values
1  | 08/26/2016   | Status: , Assistant:  | Status: Open, Assistant: John

Hope that helps a bit.

StackExchange Database Administrators Q#147997, answer score: 2