2

One of the requirement, in the complaint management system were are designing, is to implement complete history of the ticket. For example

  1. Status was changed (new to pending to work in progres to done)
  2. Ticket was assigned from one person to another
  3. Ticket need extra details
  4. Address was changed on the ticket
  5. Description of the complaint was changed

These are some of the main one and each one has corresponding field in the table. I am not sure how to go about implementing history of a ticket. Ideally I want to show what was changed, rather than show entire row, every time ticket was updated. What is the best way to do it. Should I focus on just few fields or there is a way that can implement audit log on the entire table. Note I have looked at mysql audit but I am not exactly sure if that is the right solution.

put on hold as too broad by gnat, BobDalgleish, Thomas Owens 2 days ago

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

The solution partially depends on what you want to do with the history. If it is just needed to show the changes that were made to a ticket then it's sufficient to store a JSON string describing the changed fields and their new values (in addition to the other fields that you'd need in the history table, such as ticket id, user, timestamp etc.)

If you need to support search within the history matters become more complex, and you will most likely have fields in the history table for all searchable data.

The advantage of the simple JSON based approach is that it doesn't need to be handled specially when you extend the schema of the application.

  • I just need to show changes, the ticket will keep updating. Not sure how JSON string will work. Do that needs to be a seperate file that I keep updating, and who not store this 'json string' into database, say complaint_log table – Noname Mar 14 at 15:31
  • 1
    The idea was to put that JSON string in a database column, but of course that's just one of many possible solutions, and you should pick one that you're comfortable with. – Hans-Martin Mosner Mar 14 at 18:10
1

There are usually two possible design approaches for this:

  1. Either you keep your ticket records immutable with some kind of version number attached (so whenever there is a "change" to be applied, you create a new ticket record as a copy of the previous one, but with a new version number and the changes applied)

  2. Or you model ticket changes as "change commands" and record them in a separate table "ticket_change", which has columns for the a command number (for the correct order of commands), the affected ticket (id), the affected column (its name), and its previous value. The "ticket table" itself will hold only the "final version" of your ticket.

The first approach may require more storage, but it lets you easier determine how a ticket looked like in some previous version. It also lets you map several attribute changes to "one step". The second approach may require less storage, and lets you easier determine which individual row or attribute in your ticket was changed. So pick your choice depending on the use cases you expect for the change log.

  • In my case, I am only updating the ticket but want to keep track of changes so supervisor can see what has been happening to the ticket. The second approach is what I will be looking at. I think I might have to create custom message for each type of changes. – Noname Mar 14 at 15:29

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