Helpdesk Programmers Guide

This is the Programmer's Guide for the f2w Helpdesk. It deals with installation and customisation, and explains the intention behind some of the design decisions that were made.

If you want to know how to use the helpdesk, see the User Guide. If you want to know how to administer the data, see the Administrator Guide.


  1. Installation
  2. The Database
  3. The Zope folder structure

The Database


The database design came first, when the user interface was only a vague set of ideas and I sort of thought I would be doing it in Progress. It has changed little since then, either because I built in extra flexibility at the start, or because I don't know enough PostgreSQL to do anything really clever with it.

An explanation of each table follows. The naming convention is as follows: table names are, as far as practical, single english nouns. Field names have a short prefix to identify which table they come from and keep them unique (in Zope this is an advantage because of the way it brings column names into the namespace from a query).

Users and Other Contacts contact table

contact table
k_idtextUnique contact ID (is login name for users)
k_nametextUser's actual name
k_emailtextUser's email address
k_phone_1textTelephone number 1
k_exttextTelephone extension #
k_phone_2textTelephone number 2
k_faxtextFax number
k_locationtextLocation (dept. or company)
k_webtextURL of web page, if relevant
k_next_availdatecontact is away until this date
k_commenttextComment, for any other information you need to know.

"Contacts" are any persons or other entities, who may be users of the helpdesk system, originators of requests, or external helpdesk numbers.

"Users" of the helpdesk are any people who will be allocating tasks or having tasks allocated to them. The Zope users (in /f2w/acl_users) should have corresponding IDs in this table to associate them with a name and probably an e-mail address.


team table
t_idtextteam ID
t_nametextteam name
t_leadertextUser ID of team leader

Users may be grouped into teams, or not. Teams for this purpose are just groups of users who share a set of skills or job functions. As such they can overlap.

Team Membership

member table
m_usertextuser ID
m_teamtextteam ID

Users are members of zero or more teams.


request table
r_reqintUnique request id
r_cattextRequest category (see Category table)
r_prichar(1)Request priority (see Priority table)
r_duedateDue date (deadline or review date) of request
r_originatortextThe person who requested it
r_desctextDescription of the request, unless covered adequately by the category description.
r_enteredtimestampWhen the request was created
r_entered_bytextUser who entered the request
r_statusboolWhether request is still open
r_assign_totextWho the request was assigned to
r_assign_teamtextTeam the request was assigned to

There is also a sequence called request_id used to get the next value of r_req when a request is created. A request is the basic task, request, helpdesk call, ticket or unit of work being tracked by the helpdesk system.

Request Priority

priority table
p_prichar(1)Priority code ( lower is more urgent )
p_nametextName of priority group e.g. "Critical"
p_date_nametextKind of due date (e.g. "Deadline" or "Review")

Priority codes are for coarse grouping of requests into classes according to whether and how long they can wait. For example "Critical", "Deadline", "No Deadline", "Suspended" (codes C,D,N,S). Priority is not importance, but urgency.

Request Category

category table
c_cattextCategory code
c_nametextDescriptive name of category
c_usual_prichar(1)Usual priority for requests of this kind
c_advicetextBrief advice for resolving request
c_advice_urltextURL to more detailed advice or web interface by which to complete the request.

Category codes describe (to an arbitrary degree of precision) the nature of the request. You could just not bother, and have all requests uncategorised, have a simple set of categories, or an intricate hierarchy of request categories specifying to the nth degree exactly what is being requested.

The benefit of the latter is the idea that when you have fully defined the problem you are that much closer to identifying the solution. The categories have associated with them a text field for (concise) advice, and optionally a URL pointing to more info.

In time you can build up a knowledge base for quickly resolving the commonly-occurring requests.

Questions and Answers

question table
q_statetextGuess-state for which this question is relevant
q_qnumintQuestion number
q_questiontextQuestion text

At any given point while trying to categorise a request, you are in a "guess-state" encoded as a string. Usually it will be the category code for the best guess at the requests category.

For each guess-state that isn't a final and definite identified category, there should be one or more questions, the answer to any of which will yield a better guess at the category of the request.
answer table
a_statetextGuess state
a_qnumintTogether with guess-state associates the answer with its question
a_anumintAnswer number
a_answertextAnswer text
a_nexttextNext guess-state if this answer is chosen.

For each question, there should be one or more answers. Each should result in a new guess-state, bringing the request closer to a positive identification of what is being requested.

You can think of the guess states as branch points on a tree of all possible kinds of request, and choosing an answer as moving along a branch towards the leaf that is the request category. Or not.

If your request categories form a neat hierarchy from general to specific, it's a lot easier to make this work well.

Properties - catprop and reqprop tables

I slipped out of my naming convention for these two related tables, because there was no single word that I thought would adequately describe them.

catprop table
cp_cattextCategory code
cp_proptextProperty identified
cp_labeltextLabel for the field on any forms where the value is entered.
cp_sizeintWidth of the field (again for input forms)
cp_asktextindicates whom to ask for the info (user, expert, admin etc)

A catprop defines a property (named item of information) that will always or usually be associated with requests of a particular category.

reqprop table
rp_reqintrequest ID
rp_cattextCategory this property relates to.
rp_proptextProperty identifier
rp_valuetextString value of this property of the request.
rp_dlmdatetimeDate last modified
rp_usertextUser who last modified it

A reqprop is such a property of a specific request. Note it is indexed by request and category and property code, because if a request changes category it still keeps the properties assigned to it from the category it was before, and property names only have to be unique within a request category.

So what are they for? Suppose there is a request to cancel a print job. You always want to know the print queue and the user the job belongs to. You know that, I know that, but the user doesn't necessarily, and neither does the person on the helpdesk who takes the call. Category properties appear on the form where the request details are to be entered, as a reminder to the helpdesk operator to ask about them. Saves you a phonecall now and then, and lets you do more intelligent statistical reports later if you want to.

Skillsets - Match category to team

skillset table
s_cattextCategory code or wildcard match for category code
s_teamtextteam ID,a team whose members can handle requests of that kind.

A skillset is a set of categories of request that can be handled by a team. Categories are associated to teams by a wildcard-matching string because you could have a lot of categories and a lot of users, and it would become unmanageable to record everyone's skills individually. If you have one person with a unique skill, have a one-person team.

N.B. Whether the s_cat field is matched using SQL's "LIKE" or PostgreSQL's "~=" regular expression match depends only on the ZSQL method used for the query. Use whichever you prefer.


notes table
n_idintNote ID (unique)
n_reqintrequest ID
n_usertextuser ID, of user who entered the note.
n_dlmdatetimeDate last modified
n_notetextContents of the note
n_typetextNote type

Users assigned to a task can create notes on how work is progressing, reminders of things still to do, etc. Users can only edit their own notes, but can see what other users have written about the request.

The system can also generate notes when a request is assigned, closed etc. The unique note ID is taken from a sequence "note_id".


preference table
pref_usertextuser ID
pref_typetextType of preference record
pref_keytextKey identifying preference selection
pref_valuetextValue, selected choice
pref_commenttextComment explaining choice, if needed.

The preference table is as flexible a way as I could think of to record user preferences.


For the sake of keeping the Zope front-end the same for both PostgreSQL and Oracle back-ends, some of the reports made use of a function decode that is native to Oracle but had to be defined in PostgreSQL. It gets around the lack of a conditional expression like PostgreSQL's CASE in Oracle.

decode(b1,b2,x,y) returns x if b1=b2, otherwise it returns y. It was only used in the form sum(decode(r_status,'t',1,0)) to count open requests.

Later, I decided it was cleaner to separate Oracle and PostgreSQL code because there are other syntax differences, so the function sum(bool) was defined, which effectively sums booleans as though they were the integers 1 and 0.


  1. Contents
  2. Installation
  3. The Zope folder structure
Last updated 7 Feb 2002