Introduction

I have been working on a number of projects recently that have to do with passing resources between a number of different steps. Each step has a method for determining who has permission to see the resource at that step, and who has permission to modify certain portions of the resource at that step. I am also applying for some jobs that have to do with this type of process flow control on much larger scales, so I thought that it might be a good time to formalize some of the patterns that I have found useful in implementing these kinds of systems.

This entry is going to focus largely on the database design needed to support this system. If there is interest, I can write future entries describing more of the application level design that utilizes these databases.

Problem Statement

The easiest way to describe these patterns is by the description of a practical use-case, so for the entry, we are going to design a basic ticket management system. In large enterprises, especially in the customer support departments, ticket management systems are very popular. The ticket management system provides some flow of control so that when a customer first contacts the department, a ticket is created and some information is attached to it, usually a problem description. If the first person that the customer talks to can resolve the issue on their own, then the ticket will probably only pass through that one step. Often, however, the information has to be passed to some other person, who will perform some action, add a note about it to the ticket, and then pass it back to some other person who will communicate the changes to the customer and repeat the feedback process until the issue has been resolved.

For our very basic ticket management system, we will assume five general entities:

  1. Customer – a person who has a problem
  2. Call Center – people who speak directly with the customer. Can solve basic issues like answering common questions.
  3. IT – people who handle technical problems
  4. Management – people who handle human problems
  5. Auditors – people who can look at all tickets in the system so that they can review the processes

In our case, the resource is the ticket, we have a list of groups of people, and we have a good idea of who should be able to have access and when. The customer may be able to view the entirety of a ticket about them up to its current state. Auditors can view the entirety of any ticket. Call Center, IT and Management can view a ticket only when it is at a point where they would need to, and they can only add new additions to the ticket; they cannot modify or remove previous entries on the ticket. We will assume the figuring out what group a person belongs to is trivial.

The general database design for the ticket resource will be like this:

|---------------------|          |----------------------------|
| Tickets             |          | Notes                      |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

The tickets are the main resource and is uniquely identified by its id (primary key). Each ticket will have zero or more notes attached to it, and each note can be uniquely identified by its own id. The note is associated with a ticket by the “ticket” field, which is a foreign key to Tickets.id. The original problem is stored in Tickets.problem, and any messages about the ticket are stored in Notes.message.

Status Pattern

One possible design solution I will call the Status Pattern. In this approach, we have a large table of all of the tickets in the system, and each ticket will have a “status” property. Whenever the ticket is passed to a new entity, the status is changed. In this case, we would add the “status” field to the Tickets table. The status fields can be any of the following values:

    OPEN – ticket was just created, no notes attached yet
    CALL CENTER – the problem has been stated and the call center is currently workign to solve it.
    IT – the problem has been assigned to IT to fix.
    MANAGEMENT – management has to do something to resolve the ticket.
    CLOSED – ticket has been resolved in some way, and is no longer a concern

Note that there is only one entry for “CALL CENTER”. Once IT is done with the ticket, they can pass it back to the call center, it will show up on a call center person’s list, at which point they can look at it and most likely call the customer back to tell them that the issue is supposedly resolved.

Advantages

This system has the advantage of being very simple. There is a single place to look at all tickets, and in order to generate the list of which tickets a person can see, we just look for all tickets with a status belonging to that person’s group.

Another nice feature of the system is that it is easily expandable. If we have a new group that may need to view the tickets, all we have to do is add a new option for status values.

Disadvantages

While this system is quite simple, there can be problems with it. First of all, the database will end up cluttered with CLOSED tickets over time, which will decrease the access speed for tickets that are not yet resolved. This means that the database will get slower and slower as time goes on, which is not a good thing. Archiving old tickets is a matter of going through and finding all tickets with a CLOSED status, and storing them somewhere else, but this means that we no longer have the one central location to look at all tickets both current and passed.

If you are very security minded (perhaps these tickets contain incredibly sensetive information), then there is another problem with this design: we cannot easily separate permissions across ticket statuses. A lot of database security specialists will tell you that creating a different SQL account for each user with only the permissions that user can have are essential to keeping your information secure. This is discussed in more detail in the next section where we resolve this problem.

Queuing Pattern

This solution takes a lesson from queuing theory and is the type of approach used in areas that require more severe separation of concerns. Instead of having all of the tickets in one centralized table, we will create multiple tables. For example, we will have a table that holds all of the CALL CENTER tickets, a table that holds all of the IT tickets, a table that holds all of the MANAGEMENT tickets, and a table that holds all of the CLOSED tickets. Each of these tables can have the exact same set of fields, and will essentially be duplicates of the original Tickets table. Since the tickets and the notes go hand in hand, this actually requires duplication of both tables. As a basic example, we would have tables like this:

|---------------------|          |----------------------------|
| Call_Center_Tickets |          | Call_Center_Notes          |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

|---------------------|          |----------------------------|
| IT_Tickets          |          | IT_Notes                   |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

(etc.)

With this type of database schema, the operation for passing tickets from one group to another more closely resembles its real world function. When the call center wants to pass a ticket to the IT department, the data in the Call_Center_Tickets and Call_Center_Notes tables will be duplicated into the IT_Tickets and IT_Notes tables, and removed from the Call_Center tables. Essentially, we are physically moving the ticket.

Advantages

One advantage of this pattern is that it is easy to generate the most common reports. For example, when a Call Center employee views a list of all of the tickets they need to handle, it is simply a matter of selecting everything from the Call_Center_Tickets table, joined with the appropriate Call_Center_Notes. The only exception to this rule is for an Auditor. The Auditor will have to view all of the tickets, which may require a more complex UNION across multiple sets of tables. However, since there are generally very few Audit reports generated compared to the other groups, this is normally note a problem.

Secondly, this system will remain fast over time, since only the tickets that the users care about are stored in the table that they are accessing rather than cluttering up a single table with lots of CLOSED tickets.

Third, archival of old tickets is very easy, since all of them will be stored in the Closed_Tickets and Closed_Notes tables.

A fourth and very important advantage of this pattern is security. As mentioned in the Status Pattern, a very secure system should require individual permission levels inside of the database itself. If you do not understand this concept, read this decent guide to SQL injection, and pay close attention to the segregate users section. For this database schema, we would have a tickets_call_center user, a tickets_it user, and tickets_management user, etc. Each user will have onyl the permissions that they need to do their job. For example if a client logs in to look at their ticket, the system would connect to the database with the “tickets_client” user. The tickets_client user will only have read permission on tables, so that even if the user is able to find an application vulnerability in the system, they would not be able to change any of the tickets. A call center employee would be connected to the database using the tickets_call_center user, and would have INSERT and SELECT access to the call_center_tickets and call_center_notes tables so that they could create tickets and notes, and then DELETE access to the call_center_* tables and INSERT access to other tables so that they could pass the ticket on to other groups. In the worst case scenario, if they call center employee turns malicious and finds an application level vulnerability, they would only be able to affect call center tickets, and these they would only be able to delete. This helps to contain the damage they can do.

Disadvantages

However, this extra speed and security does not come without some sort of price paid in overhead. As mentioned before, the Auditor user will have to use more complicated queries to view all of the tickets together. This is not usually a major concern. Secondly, the system is significantly more complex. If it is coded properly with the correct use of transactions, there will not be any lost tickets when moving tickets between statuses, but this requires good design and a lot of testing to make sure it is correct. Also, there is a lot of duplicate types of information here that formal models for normalization may not take kindly to.

Pointer Pattern

As with any system, there are many possible solutions, and I cannot cover all posibilities, however this last pattern is another alternative to the previous two, and is in a way a hybrid of the two. With this pattern, we would store all of the tickets in a centralized pair of tables called simply “Tickets” and “Notes”. However, instead of having a status field to manage the permissions, we can have a set of individual tables for each type of user. One could be named “call_center”, and would simply be a list of all of the Tickets.id values that correspond to tickets that are currently assigned to the call center. This pattern would be duplicated by tables such as “it”, “management”, etc. This allows us to emulate the real world process again because we can pass a ticket from one group to the next by removing it from one list and adding it to another. However, this pattern does not provide any advantage over the previous two since we cannot secure permissions between entities, and we are required to do JOINS across tables for any sort of request. I am mentioning it only because I have seen it used.

Conclusion

Keep in mind that these example are simplified. In the real world, you will need to take into account other facts such as making sure that call center employees can still view older tickets in case the person calls back a long way down the line. Your choice of the pattern to use depends a lot on personal preference, on the abilities of your DBMS, and on the application where it is being used. For example, some DBMS’s may allow you to have more complex permission schemes, so that you can properly secure data in one table rather than having to separate it across multiples ones. When you take this into consideration, the Queuing pattern may not have as many advantages. Any professional application development requires some judgement on the part of the developer, and there is no silver bullet.