Is The Gc.com Database Scheme Available ?

[+sTeamTraen]

At my place of work, we have a requirement for an internal CRM application which lets people ask the IT department to fix their computer, the technical services people to fix their light bulbs, HR to authorise vacation time, etc.

 

Currently we have a "v0.1" application which accepts requests, but the big complaint is that people can't track what's happening. As a result, it's only used for IT problems, where the "tracking" consists of "if we don't call you back in 4 hours, phone us".

 

I have had the job of fixing this dropped on me.

 

It occurs to me that the geocaching.com site corresponds to most of our requirements. If a cache listing corresponds to a user request, then the user/approver logs would correspond to various comments by people executing the request. Both the requestor ("cache placer") and the various staff involved ("cache finder") could upload "photos" (Word documents, etc) to enrich a given request.

 

Now, we are an international, not-for-profit organisation, so we wouldn't be able to pay squillions to license the site code. Nor would we want to: for one thing, all our new developments are on open source, rather than the Microsoft platform, and for another, our requirements are both 5 times simpler and 3 times more complex than those for geocaching.

 

But if there's some documentation somewhere on how the GC.com database hangs together, that would be a big help in deciding if the model of a database which generates HTML pages, is we want to follow. If necessary we could sign any required non-disclosure papers, etc.

 

Thanks in advance for any ideas,

 

Nick Brown

Council of Europe (www.coe.int)

Strasbourg, France

[+Tidalflame]

What's your app written in? I imagine there's probably a better example to follow somewhere on the web... based on how you're describing it, it doesn't sound very complex...

[+Team DEMP]

If you haven't already, you might want to look at some open source trouble ticket systems as I imagine they all have a way to track activity. An example would be http://www.osticket.com/ where I'm sure you can see the underlying database schema. Some more listed at http://www.help-desk-software.info/help-desk-comparison.html and I'm sure plenty listed at Google for open source trouble ticket.

[+sTeamTraen]

Thanks for those suggestions, but we need something beyond a help desk ticket system. We have more or less abolished our phone help desk, so there's nobody to fill in the ticket apart from the user. We need a way for a user who has zero training, and who is stressed because, say, their e-mail isn't working, to get a request to us. But we also need the same system to allow their boss to ask for a new computer to be installed for a new hire, and it should also be extensible so that in the future, we can build a form so people can ask for their office to be redecorated, or special leave because their hamster died, or whatever.

 

The most important thing is that, having submitted the request, the user can then track it, and see how we are making progress towards fulfilling it. The GC.com site is pretty close to how we would do this!

[+Markwell]

This really should be in the Off-Topic area, as I think the chances of the GC.com database being opened for this purpose is "a Buh-Zillion to 1".

 

From the IT perspective...

 

We use Track-IT! which links to people sending an e-mail to Computer Help. Our MS Exchange Server forwards those e-mails to Track-IT! and the system logs it as being unassigned to a user.

 

The system generates an automatic response, and are viewable by any of the IT staff, but our 2 tier one support people periodically glance through and either assist in the problem or bump it up the support ladder to the person that best can help the situation. At that time, the tier one support also assign a "category" to the issue for later tracking.

 

Once the ticket is complete, the technician is supposed to write something about the solution in the resolution area so that the tier one people can look up past resolutions to see if there's something that THEY can do to help when it comes up again.

 

This way the system can also track if mundane problems are being bumped up to tier 2 or 3, and if there needs to be more education on the tier 1 support.

 

And BTW - I would strongly suggest moving back to phone support. Even with this system in place, the best comments we get are with how friendly and helpful our front line people are at our Help Desk.

Edited August 23, 2005 by Markwell

[+sTeamTraen]

>>I think the chances of the GC.com database being opened for this purpose

>>is "a Buh-Zillion to 1".

 

Indeed. I was just hoping for a brief technical overview of how some of the major elements (caches, logs, players) are structured.

 

>>And BTW - I would strongly suggest moving back to phone support. Even with

>>this system in place, the best comments we get are with how friendly and

>>helpful our front line people are at our Help Desk.

 

Since we got rid of phone support, our MTTR user problems has gone down by two-thirds. We determined that it was an extremely inefficient way of handling minor user problems. YMMV depending on how technically competent your help desk staff are (ours were recycled phone operators who couldn't be fired because we're worse than the government in terms of job security for life) and what kind of problems your users call with (we have a lot of people who used to call the help desk for a chat when they were just having a bad day - again, largely incompetent staff who should probably have been fired but couldn't be). It meant that when a competent individual called with a genuine problem, it took a long time to identify them as such, and by that time, the problem had often become critical.

 

The "off-line" support model has become so successful and accepted that users now want to use it for all their interactions with the admin part of the organisation: filling in their expenses, booking vacation time, etc. We want to give them a "one-stop shopping" interface to all admin services, so it goes way beyond dispatching help desk calls now anyway (beware of snowballs ).

[Elias]

Indeed.  I was just hoping for a brief technical overview of how some of the major elements (caches, logs, players) are structured.

Unfortunately, there's no magic in how our database is set up. It looks just like you'd expect: account table, cache_detail table, cache_log table, and a whole bunch of other ancillary tables.

 

All the site's work is done in the code, and while we've separated the application from the presentation layer, reskinning the site for some other use would be a monumental task. That is, if we even wanted to.

 

So I'm afraid I can't be of much help with your application. But I do agree with the comments others have been making - I can't imagine that you really need to build your application from scratch. There are lots of well-supported tools, even if for slightly different markets that should easily serve your need. Because I like it so much, I'm going to plug the one we use...

 

We use FogBugz for our internal bug tracking system. While FogBugz is designed for software development bug reporting and tracking, its an extremely flexible tool that I believe would cover more than 90% of what you're looking for. It can accept new "cases" via email or a publicly available web form, and those cases can be categorized and sorted in just about any way you can imagine. It should be very easy to set it up to handle the examples you gave.

 

Anyway, sorry I can't be of more help. I really think that by trying out some of the tools that have been suggested here to see if they can fit your needs, that you can save yourself a lot of time an energy by not trying to build one from scratch.

 

Elias

[+Lil Devil]

My company uses RT: Request Tracker which will probably do everything you want, and much more. You can use it unsupported for free, or you can pay for support.

 

Edit to say that now that I've read *all* of the above posts, and not just the first couple, I'll guess that RT will do about 90% of what you need. But since it's open-source, any competent programmer can then edit it to get that other 10%. Far better to start with 90% than start from scratch, IMO.

Edited August 23, 2005 by Lil Devil

[+sTeamTraen]

My company uses RT: Request Tracker which will probably do everything you want, and much more. You can use it unsupported for free, or you can pay for support.

Thanks, I'll check that out. At first glance I'm not sure if it's what we need because it doesn't look like you can allow any total idiot to create a ticket - we screen a lot of our calls out because users don't fill in half the form properly (and we have to "avoid conflict" with them).

 

On the other hand, any company with a product call RTFM has to be pretty good!!

 

Nick

[+Prime Suspect]

It sounds like project tracking software (rather than trouble ticket) might better suit your needs. There's a lot of it out there, and a lot of it's freeware.

[+caderoux]

We built our own.

 

One of the features most useful is that users can submit requests for other users (like if their computer is down, or their account is locked out).

 

In addition, requests for new hardware or software is automatically copied to their boss for approval.

 

Prioritization is not allowed by users, since they are not able to balance the workload across departmental needs.

 

We also use it for security authorizations, equipment loans, and new user account requests.

[+Tidalflame]

On the other hand, any company with a product call RTFM has to be pretty good!!

Heh, I just noticed that. How terribly ironic. I wonder if they intended it as a joke, or if nobody knew about the acronym?

[+sTeamTraen]

On the other hand, any company with a product call RTFM has to be pretty good!! 

Heh, I just noticed that. How terribly ironic. I wonder if they intended it as a joke, or if nobody knew about the acronym?

Since it manages FAQs, I guess it was somewhere between conspiracy on the part of the developers, serendipity, and a little bit of humour. Nice to see that they aren't overly corporate. Of course, when they get big, they'll have to change it, when someone gets on their case for corrupting children.

[+stevetx1972]

We use a tracking system called PerfectTracker, http://www.avensoft.com/.

 

Not free, but not expensive and scaleable with the things that you want to do.

 

-S.

[+sTeamTraen]

Thanks to all who chipped in with ideas.

 

In the end, we've decided to write our own, in PHP. So far prototyping has been going on for about a week and we already have a database and some forms and queries which are crude, but enough for a "proof of concept".

 

The whole thing looks quite a bit like this site:

- A request works something like a cache

- A user (or operator) intervention works like a log - anyone can write one at any time

- Nothing is ever deleted - just marked "done" (archived)

- You can sign up for various degrees of notification by e-mail

 

Certainly, without the example of geocaching.com, I would never have come up with this design!

Edited September 12, 2005 by sTeamTraen

[+The Leprechauns]

Please include a "DNF" ("Did not Fix") log option. Otherwise, my company's help desk would rarely get to make an entry in the database!