Background
I recently had the opportunity to meet a new owner of a local communications company and one of the things that was mentioned in the meeting was a ticketing solution based on the needs of the company. It was quite funny as not a week prior I had been discussing a custom built system built on top of a SQL database with the operator of a answering service and I myself had already been working on my own ticketing system. The issue I realized during this meeting, my focus was solely on service and not a complete solution for sales and service. Thus, my exploration into a more modular solution to fit more needs.
What is a database?
To put it simply, a database is a place to store information. Either standalone or relative. The easiest way to look at it is as a spreadsheet (specifically Microsoft Excel). The document itself is a database with rows and columns. The sheets are akin to tables and using some functions, you relate information on one sheet to another.
I’m going to be using MariaDB for my database server. MariaDB is a for of MySQL which is an open source variant of SQL. For this post, I am going to assume MariaDB (or MySQL) is already installed and user privilege’s are already set.
Goals
We need to set forth some goals that the ticketing system needs to accomplish. This can change later on (one of the reasons for building things as modularly as possible).
- Ticket tracking – Auto-creation of a ticket number, customer that ticket refers to, date the ticket was opened and the date the ticket was closed as well as who is responsible for the ticket and the type of ticket.
- Customer database – Basically a minimal CRM that can correlate a customer ID to a POC with contact info as well as the employee responsible for the account.
- Employee database – need to keep track of who is responsible for tickets and accounts.
- Ticket types – a simple relation.
This will provide a start for the ticketing system and each goal is going to become it’s own table. This entire database will also grow as we add more items to the system such as quotes, service, etc.
Getting Started
Let’s start by creating our new database (all commands are via the command line but the database can be built using a GUI front end as well).
CREATE DATABASE tickets;
USE tickets;Now lets create our first table, the employee table (as it does not rely on other tables).
CREATE TABLE employees(
employeeID INT AUTO_INCREMENT,
employeeName VARCHAR(50) NOT NULL,
employed BOOLEAN NOT NULL,
PRIMARY KEY(employeeID,employeeName)
);What this command does is create a table named employees with three columns. One for the employee ID number (automatically assigned), one for the employee’s name (which cannot be null) and a simple Boolean operator (either 0 for false or 1 for true) of whether or not the employee is currently employed. Populating this table would look something like this.
INSERT INTO employees (employeeName, employed) VALUES ('John Doe',1);Next, we will create our ticket types table.
CREATE TABLE ticketTypes(
type CHAR(1) NOT NULL,
name VARCHAR(50) NOT NULL,
PRIMARY KEY(type,name)
);This table simply correlates a single character to a type of ticket. Examples types may be:
| E | ENGINEERING |
| I | INSTALL |
| N | INQUIRY |
| Q | QUOTE |
| R | REPAIR |
| T | TROUBLESHOOT |
Now we can create our first table with a relationship, the customers table.
CREATE TABLE customers(
customerID INT AUTO_INCREMENT,
customerName VARCHAR(50) NOT NULL,
poc VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone INT(10),
rep INT NOT NULL,
PRIMARY KEY(customerID,customerName),
FOREIGN KEY(rep) REFERENCES employees(employeeID)
);Using the Foreign Key command, we’ve create a column (rep) which is dependent on the employeeID column in the employees table. This means, a customer cannot be created unless a rep is assigned which is currently in the employee table with a valid employee ID.
Now that all the setup has been done, we have two final ticket related tables to create. First, the primary ticket table which will contain the automatically assigned ticket number, the customer number, ticket type, description, the date the ticket was opened, the date the ticket was closed and the responsible employee.
CREATE TABLE ticket(
ticketID INT AUTO_INCREMENT,
customerID INT NOT NULL,
ticketType CHAR(1) NOT NULL,
ticketDescription TINYTEXT NOT NULL,
openDate DATE NOT NULL,
closeDate DATE,
rep INT NOT NULL,
PRIMARY KEY(ticketID),
FOREIGN KEY FK1(customerID) REFERENCES customers(customerID),
FOREIGN KEY FK2(ticketType) REFERENCES ticketTypes(type),
FOREIGN
KEY FK3(rep) REFERENCES employees(employeeID)
);This is one of the more complex table creations due to the three independent foreign keys (FK1, FK2, and FK3). This is also the last real table needed to get the ticketing system operational but I feel it could use some more detail so we will add one more table called ticketDetail.
CREATE TABLE ticketDetail(
ticketID INT NOT NULL,
time TIMESTAMP,
employeeID INT NOT NULL,
detail TEX
T NOT NULL,
filename VARCHAR(50),
files BLOB,
PRIMARY KEY(ticketID,TIMESTAMP),
FOREIGN KEY(ticketID) REFERENCES ticket(ticketID)
);This final table simply provides a location for more information to be provided. It allows for detailed text to be input, who input it and when it was input and any associated files can be added in as well.
Conclusion
Now the ticketing system can begin working for us. Fill out the employee ID table, customer table and ticket type table and then create the first ticket. The detail ticket table is optional but provides a place for updated information and detail on a ticket to be provided. By default, the close date will be NULL on tickets (meaning any ticket with NULL for a close date is to be considered open). This ticketing database can also be easily expanded (thanks in part to it’s modular tables).
