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.
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).
contact table
| Field | Type | Description |
|---|---|---|
| k_id | text | Unique contact ID (is login name for users) |
| k_name | text | User's actual name |
| k_email | text | User's email address |
| k_phone_1 | text | Telephone number 1 |
| k_ext | text | Telephone extension # |
| k_phone_2 | text | Telephone number 2 |
| k_fax | text | Fax number |
| k_location | text | Location (dept. or company) |
| k_web | text | URL of web page, if relevant |
| k_next_avail | date | contact is away until this date |
| k_comment | text | Comment, 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.
| Field | Type | Description |
|---|---|---|
| t_id | text | team ID |
| t_name | text | team name |
| t_leader | text | User 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.
| Field | Type | Description |
|---|---|---|
| m_user | text | user ID |
| m_team | text | team ID |
Users are members of zero or more teams.
| Field | Type | Description |
|---|---|---|
| r_req | int | Unique request id |
| r_cat | text | Request category (see Category table) |
| r_pri | char(1) | Request priority (see Priority table) |
| r_due | date | Due date (deadline or review date) of request |
| r_originator | text | The person who requested it |
| r_desc | text | Description of the request, unless covered adequately by the category description. |
| r_entered | timestamp | When the request was created |
| r_entered_by | text | User who entered the request |
| r_status | bool | Whether request is still open |
| r_assign_to | text | Who the request was assigned to |
| r_assign_team | text | Team 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.
| Field | Type | Description |
|---|---|---|
| p_pri | char(1) | Priority code ( lower is more urgent ) |
| p_name | text | Name of priority group e.g. "Critical" |
| p_date_name | text | Kind 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.
| Field | Type | Description |
|---|---|---|
| c_cat | text | Category code |
| c_name | text | Descriptive name of category |
| c_usual_pri | char(1) | Usual priority for requests of this kind |
| c_advice | text | Brief advice for resolving request |
| c_advice_url | text | URL 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.
| Field | Type | Description |
|---|---|---|
| q_state | text | Guess-state for which this question is relevant |
| q_qnum | int | Question number |
| q_question | text | Question 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.
| Field | Type | Description |
|---|---|---|
| a_state | text | Guess state |
| a_qnum | int | Together with guess-state associates the answer with its question |
| a_anum | int | Answer number |
| a_answer | text | Answer text |
| a_next | text | Next 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.
catprop and reqprop tables
| Field | Type | Description |
|---|---|---|
| cp_cat | text | Category code |
| cp_prop | text | Property identified |
| cp_label | text | Label for the field on any forms where the value is entered. |
| cp_size | int | Width of the field (again for input forms) |
| cp_ask | text | indicates 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.
| Field | Type | Description |
|---|---|---|
| rp_req | int | request ID |
| rp_cat | text | Category this property relates to. |
| rp_prop | text | Property identifier |
| rp_value | text | String value of this property of the request. |
| rp_dlm | datetime | Date last modified |
| rp_user | text | User 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.
| Field | Type | Description |
|---|---|---|
| s_cat | text | Category code or wildcard match for category code |
| s_team | text | team 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.
| Field | Type | Description |
|---|---|---|
| n_id | int | Note ID (unique) |
| n_req | int | request ID |
| n_user | text | user ID, of user who entered the note. |
| n_dlm | datetime | Date last modified |
| n_note | text | Contents of the note |
| n_type | text | Note 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".
| Field | Type | Description |
|---|---|---|
| pref_user | text | user ID |
| pref_type | text | Type of preference record |
| pref_key | text | Key identifying preference selection |
| pref_value | text | Value, selected choice |
| pref_comment | text | Comment explaining choice, if needed. |
The preference table is as flexible a way as I could think of to record user preferences.
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.