Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Storing Data Suggestions

 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am converting my Help Desk app at work and I thought while I was doing so I would take this opportunity to better my Tables and how data is stored.
So I guess I am looking for some suggestions on how I should be organizing this stuff.
The main data I am interested in dealing with right now is when a customer calls with a problem and how to store there data.
There are 2 different things that can happen.
1. Caller calls and gets a resolution over the phone. This is simply entered as a Call Log
2. Caller calls and a technician needs to visit them. This would be entered as a Trouble Ticket.
So I am debating on how much I want to split the information up. Here are some options.
A. I could put everything in one table. And basically if it is a ticket I have a Ticket Number, else, TicketNumber == NULL so I know it is just a Call Log
B. Seperate Call Log and Ticket Data. This would consist of everything that is considered part of a Call Log in 1 table. Anything that is Ticket Specific (assigned Tech, accepted Tech, Date Promised, etc) would go in a seperate table.
C. Actually have 3 tables. A Call Log table with specific caller information, a Ticket Table with Ticket specific information, and then a Problem/Solution Table that only consists of the Problem and the Solution (don't know why I would want to do this, but thought I would throw it out there).
I would also like to keep in mind that I may need to add additional fields as time goes on and need it to be able to make this as easy as possible.
I welcome any suggestions. Thanks.
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd go 3rd normal first and then see if any optimization is required. If you saw things in code like "if ( null != ticketnumber ) use the rest of the columns )" you'd be violating the "columns depend on the key only" rule so at least the two-table solution sounds warranted.
I like the problem/solution table. Keep track of how often it is referenced (increment a counter) and it becomes a smart FAQ. You might have a tricky time of identifying duplicate problems or forcing the user to link to an existing problem.
 
John Dunn
slicker
Ranch Hand
Posts: 1108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would see how long it takes to make a call log a ticket, and possibly combine them. All the extra fields could default to "Call Log" options.
You could then search off the call log, if you wanted to do so.
 
Scott Ambler
author
Ranch Hand
Posts: 608
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would get your classes right first, then do a simple mapping of your classes to your tables (check out www.agiledata.org/essays/mappingObjects.html). This will lead to a relatively normalized set of tables. Next step is to performance profile the app, if it runs fast enough then leave it alone. If not, refactor (www.agiledata.org/essays/databaseRefactoring.html) your schema(s) as required, updating your mappings appropriately.
- Scott
 
Jason Steele
Ranch Hand
Posts: 100
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If a call comes into the help desk and it is trouble issue, regardless of wether it is resolved on-the-spot or not, a trouble ticket should be opened. In this manner there should be no differentiation between a call logged and a trouble ticket issued.
if a call comes in open a ticket, log the issue, assign the issue to the help desk individual who took the call, enter the resolution, timestamp it now(), mark the ticket resolved, close the ticket. Then, you know there was an issue and you get credit for solving the issue, even if it was just a quick fix.
Jason Steele
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jason Steele:
If a call comes into the help desk and it is trouble issue, regardless of wether it is resolved on-the-spot or not, a trouble ticket should be opened. In this manner there should be no differentiation between a call logged and a trouble ticket issued.
if a call comes in open a ticket, log the issue, assign the issue to the help desk individual who took the call, enter the resolution, timestamp it now(), mark the ticket resolved, close the ticket. Then, you know there was an issue and you get credit for solving the issue, even if it was just a quick fix.
Jason Steele

I think this is the approach we have decided to take. It will make the database schema a bit simpler I think.
Thanks for all the suggestions.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic