I’m currently in the process of writing a support ticket system… Let’s say it’s a small forum application, or something like Uservoice.
Now I want my users to be able to edit their tickets, but still be able to see the old versions of them.
Which route should I take in terms of Database-Design?
I’ve thought of something like:
SupportTickets
- Identifier
- Author
- Subject
- SupportTicketContentId
SupportTicketContents
- Identifier
- Content
- CreatedOn
Now when viewing a ticket, I’d simply query the most recent SupportTicketContent depending on the CreatedOn column…
But is there a better way? Or is there a better naming of these tables?
6
Working from Redmine and its database design you have a ticket, a journal item, and any details associated with that.
+--------------+ +----------------+ +----------------+
| ticket | | journal | | journal_details|
|--------------| |----------------| |----------------|
| id +-+ | id +-+ | id |
| author | +---+ ticket_id | +-+ journal_id |
| subject | | author | | field |
| priority | | timestamp | | new_value |
| ... | | notes | | old_value |
| | | | | |
+--------------+ +----------------+ +----------------+
You don’t copy the entire issue each time, but rather you keep all of the ‘header’ and meta data information up to date in the ticket table.
Then, for updates to it (additional notes and any field changes) you create a row in the journal table. If all you did was add notes, you just add a journal entry. If, however, a field changed (like the priority) a journal entry row is added, and a journal_detail row for each row changed.
Say you have ticket #123 and you:
- Add notes (‘this is actually bar’)
- Change the subject from ‘foo’ to ‘bar’
- Change the priority from ‘critical’ to ‘low’
The following things would happen:
- Journal row is added with with the author as you, timestamp as now, ticket_id as 123, and the notes as ‘this is actually bar’. Lets say this row has an ID of 456.
- A journal_detail row is added with jounral_id of 456, field of ‘subject’ new_value of ‘bar’, old_value of ‘foo’
- A journal_detail row is added with journal_id of 456, field of ‘priority’ new value of ‘low’, old value of ‘critical’
- Ticket is updated with subject of ‘bar’ and priority of ‘low’
This way, if needed, one could reconstruct the past and see who did what when. But few people want to see what it really looks like a month ago – they want fast queries and displays against current data.
Short Answer: Honestly, creating a new ticket tracking system from the scratch is tempting and cool! However, I would try stay away from re-inventing the wheel.
One of the good approaches would be clarifying all business requirements and look for available open-source alternatives. Here you are several alternatives to start with:
- ASP.net based open source support ticket system
- BugTracker.NET
- Slick-ticket.com/
- Ticket Desk
4