Ticketing System -- Opinion on Customers Table, Time Billing
The Ticketing System
I'm working on this ticketing system in PHP. The goals of the project are:
* Be as extremely useful, yet logical and minimalistic at the same time. This is KISS-method to the extreme. This is because I think most ticketing systems you download today are too complex and therefore too hard to customize. That's the impetus for me to write this in the first place. I thought that I could get wider adoption by building something powerful, yet ingeniously simple at the same time.
sidebar: In fact, the installation is also extremely simple -- you just drop it in a web directory and hit the pages -- it recognizes stuff it's missing and starts building these things for you. When it fails to do this, it is going to show you a set of web pages with pictures so that you could do these extra tasks yourself. How many ticketing systems have you downloaded that were a pain in the rear for you in getting them setup? Wouldn't you like to turn that into a 5 minute operation instead of a 2 hour one?
* It should be useful as a helpdesk ticketing system out of box, but then be available as an SDK to take it to another level, such as a full-bore CRM or ERP application. Sure, it's missing a lot of those features and has very few tables, but because it is, you can envision adding those more clearly, and besides, in many companies, they have all these other tables on other systems that are already built and which they want to integrate with. You don't want to create parallel tables for those same elements. You want to, instead, remove those elements and leave them as holes (or should I say sockets) for the programmer who sees this project to plug his backend systems in with.
* Should use the least number of pages and lines of code possible to achieve the necessary effects. In fact, future revisions after production release will focus on code refinement over bug fixes and new features until the code is so sweet-looking that you'd even "rinse your mouth with it". Then, and only then, will I return to bug fixes and newer features (but only a handful of newer features will ever make it into this project).
* Should be possible to bill time against such a ticketing system.
* Should use sensible normalization (that means database normalization theory, BTW), but not too much. Many ticketing systems normalize the hell out of everything, causing slow table reads. I'm not a fan of that at all. With millions of tickets, I want a super-fast ticketing table. There's also referential integrity risk if you normalize everything to the hilt and have a small bug that does not clean up other linked tables when a foreign reference table entry is deleted.
sidebar: One thing I've learned after many years of managing programmers and being one myself is that newbie "expert" programmers often love to normalize the hell out of everything. Even Codd himself, the IBM inventor who invented SQL, realized that too much normalization can slow certain kinds of tasks down, and therefore some tasks require denormalization of tables. Therefore, I normalize where it makes sense, and denormalize where it also makes sense. Most newbie "expert" programmers do not grasp this, no matter how many times I talk to them about this.
The Customers Table Problem
My biggest problem has been the customer's table.
Previously in my system, you could just type in the customer's name and either a phone or email address. I was moving steadily for a couple years that way when I started looking at other ticketing systems on the web. I noticed that theirs had a way to actually do billing against those tickets. I thought, "If anyone can type in any non-standard, inconsistent way of putting in a customer's info, how can you bill against that?"
So therefore I knew I had to introduce a customer table. But I hate those, actually. Let me back up a bit and explain why. First, I have 3 ticket tables. They are ticketsopen, ticketsvoided, and ticketsresolved. Out of this, I derive "queues" which are views for particular workgroups of workers, or perhaps just a queue for a particular worker. In those tables, I designed them so that they were not normalized (we're talking database normalization theory here) very well. The reason for this was because I wanted blindingly fast reads. I also hate the referential integrity problem where you delete a foreign key's record and then have to regressively work that change all through the app, on the fly. Instead, I used foreign keys conservatively where they made sense. Some may argue, dude, not all your tables are in some kind of normalization mode I prefer, and I would retort, yeah, but they can be read a hell of a lot faster and you don't risk referential integrity problems when you remove an entry from a foreign reference table.
God, I hope that makes sense to you, because it's very hard to explain. Anyway, if I introduce a customer table, then that means I must drop a foreign key in my ticket tables to link to that customer table. So then this begs a few more questions:
Q1. What happens when someone decides to switch the customer information out, midstream, after they picked the customer from the customer table and moved on to typing the ticket? Do I want it to go back and change the customer record?
A1. The answer I came up with, so far, is no. The thing it looks like I'm going to have to do is include the foreign key for billing purposes inside the ticket tables, but then also redundantly carry forth the customer record data in the ticket tables as well. That way, if a particular ticket needs a special deal where the customer data is edited, I can let them do it without screwing up the original customer record. Meanwhile, I can provide another set of pages for doing maintenance on customer records.
Q2. What happens when someone uses customer X in the ticket, but then realizes the mistake and want to switch to customer Y? Do I really want to write all these pages that allow them to go grab another customer record and relink it with the ticket? My project was almost finished and I'm not looking forward to having to write more pages. I also fear the day when someone deletes a customer record and, by some grand blunder I may have left behind, the tickets linked to that customer become orphaned, never ever showing up in the query again, even though they exist in the ticket tables.
The Time Billing Problem
How do I adequately bill time against a ticket in a minimalistic way? For one thing, the way I handle my work items inside a ticket is by just one big work field. Each work item is delineated by a special timestamp that looks something like:
[username timestamp]: my work item text goes here
I like it better that way. I also am not a fan of ticket timers because they can be highly inaccurate. Therefore, to bill against this, I was going to assume that each work item takes a total of 15 minutes to perform. I was going to count (using regexps) how many of these timestamps are in the work field and multiply that times 15 minutes.
But wait! Before you freak out on such a technique, before the ticket is resolved and moved to the ticketsresolved table, I was going to have an interface where I show you the assumed cost of time on the ticket and the ticket operator (worker) has the chance to edit that into another value before it it is finalized.
As for a customer's tech support rate info, I was going to store it in a column in the customer's table. One customer could be charged $4 an hour for work, while another could be charged something like $8. Then, in the ticket, I was going to include a foreign key reference to that customer.
Therefore, if I have a ticket and 4 work items in it, then that's 4 * 15 * $rate. That becomes the assumed value. Then, when you close the ticket out, you are given this total time value and you can edit it to what you feel it really was in actuality.
Meanwhile, tickets also have an open and closed timestamp on them, and I was going to provide that as an optional value that a worker could at least see, so that they could use that as a guide in determining time spent on conducting the work.
Meanwhile, there are other concepts I have to consider, such as a cap-time on work, such as never bill a customer more than $150 for a ticket, or only bill a certain customer $150 on a ticket no matter what, or only on certain kinds of tickets. These are things I still have to think about and work on.
So that's my big honking project in a nutshell. It's a ticketing system with a huge customer table problem and a time billing problem, and I need to apply it with the core principles of the project in mind. Feel free to comment.