Venu Yerra
MSDN Content Development Group
November 1997
Updated: February 2, 1998
Update details The sample database that accompanies this article has been updated to represent the final state of the HelpDesk sample application.
Click to copy the sample HelpDesk database.
This article explains the database model created for the HelpDesk Site sample. The sample database associated with this article was created in Microsoft® SQL Server™ version 6.5. This article will cover the following:
The HelpDesk Site is a Web-based, object-oriented sample application developed by the MSDN Content Development Group. It consists of a database, a scalable back-end, and clients that support a variety of methods for submitting help requests, a process for optimizing the assignment of technicians to each request, and a request-resolution cycle. Robert Coleridge's article "The HelpDesk Site Sample: Overview of Automated Solution" gives a good introduction to the HelpDesk sample. Also, read "Designing the HelpDesk Transaction-Processing Object Model" by Fred Pace to learn about the HelpDesk object model and "Task Distribution in a Customer Service System" by Steve Kirk to understand the task-distribution scheme.
The HelpDesk sample includes clients for both the user (requestor) and the technician. The clients do not access the database directly; they use the HelpDesk objects, as illustrated in Figure 1.
Figure 1. The HelpDesk object model
HelpDesk is designed to manage easily and efficiently the resolution of Help requests. All requests are stored in a centralized database. These requests are then assigned to different technicians to be resolved.
The central entity in the database is a user's request for help. A request is assigned to a technician based on the skill level required and the location of the requestor and the technician. The technician considers factors such as the priority and date of the request when deciding the order in which to resolve assigned requests. Therefore, all requests have the following attributes set at insertion time: priority, requested date, location, skill, description, and e-mail alias of requestor Similarly, each technician is described by a set of attributes in a technician table: skill, location, and e-mail alias. A third table stores the details of all communication between the technician and the requestor that occurs during the resolution process.
All other tables are in some way related to these tables. They wrap around the main tables providing consistency and reducing redundancy in the database.
Figure 2 illustrates the HelpDesk schema and clearly shows how other tables in the database are linked to the three primary tables.
Figure 2. The HelpDesk database schema
The database consists of three primary tables:
Each record in this table corresponds to a request that has not been resolved. Records get inserted into this table in one of the following ways:
After a request is resolved, it is inserted into the tbReqHist table, and then removed from the tbReq table. Table 1 lists the fields of tbReq.
Table 1. tbReq Fields
| Field Name | Data Type | Attribute | Description of Field |
| PKId | Int | NOT NULL | Primary key of the request record. |
| ReqDate | Datetime | NOT NULL | Date and time the request is made. |
| ResolDate | Datetime | NULL | Date and time the request is resolved. |
| LocId (FK) | Int | NOT NULL | Location of the request. Foreign key to tbLocation. |
| PriorityId (FK) | Int | NOT NULL | Priority of the request. Foreign key to tbPriority. |
| ResolById (FK) | Int | NULL | Technician who resolved the request. Foreign key to tbTech. |
| ResolTypeId (FK) | Int | NULL | Type of Resolution. Foreign key to tbResolType. |
| SkillId (FK) | Int | NOT NULL | Skill type required. Foreign key to tbSkill. |
| StatusId (FK) | Int | NOT NULL | Present status of request. Foreign key to tbStatus. (For a list of possible statuses, see the "Status" section) |
| TechId (FK) | Int | NULL | Technician who is assigned to request. Foreign key to tbTech |
| TrackingNo | Int | NOT NULL | Used to track request record through the entire lifecycle1 |
| EmailAlias | Varchar(12) | NOT NULL | E-mail alias of the person who requested help |
| ResolOther | Text | NULL | Other details |
| Descr | Varchar (50) | NULL | Description of request |
1. As we have already seen, requests can come into tbReq from tbMsgIn table. To identify the request between these two tables, TrackingNo is used. Also, in all interactions between technician and user, TrackingNo is used to identify the request. Finally, TrackingNo identifies the request once it has been resolved and moved into tbReqHist.
This table contains request details. PKId is the primary key on the table that identifies each request detail record.
The ReqDetTypeId identifies the type of request detail: a standard text response, a text response, a standard voice response, or a voice response. Table 2 lists the fields of tbReqDet.
Table 2. tbReqDet Fields
| Field name | Data type | Attribute | Description of field |
| PKId | Int | NOT NULL | Primary key to request detail record. |
| ReqId (FK) | Int | NOT NULL | Request to which this request detail belongs. |
| ReqDetTypeId (FK) | Int | NOT NULL | Type of request detail. Foreign Key to tbReqDetType table. |
| DetMsgId | Int | NULL | Key to table to which request detail type is pointing. 1 |
| TechId (FK) | Int | NULL | Technician who is involved with this message. |
| DetDate | Datetime | NOT NULL | Date and time when this request detail was created. |
1. A record is inserted into tbReqDet every time the user or technician responds about the request. This can be an e-mail or voice response. A record is also inserted into the tbTxtReq, tbVoiceReq, tbStdTxtResp, tbStdVoiceResp, tbTextResp, or tbVoiceResp tables depending on the type of response. The DetMsgId field will have the key that refers to any of one of these tables.
This table contains information about each technician in the HelpDesk system and has a primary key (PKId) for each technician. Table 3 shows the structure of the tbTech table.
Table 3. tbTech Fields
| Field name | Data type | Attribute | Description of field |
| PKId | Int | NOT NULL | Primary key of technician record. |
| LocId (FK) | Int | NOT NULL | Foreign key to tbLocation. |
| SkillId (FK) | Int | NOT NULL | Foreign key to tbSkills. |
| EmailAlias | Varchar (12) | NOT NULL | E-mail alias of technician. |
| StatusId (FK) | Int | NOT NULL | The status of technician (logged on or logged off). Foreign key to tbTechStatus table |
All other tables in the database support the primary tables. For example, the different priority levels are kept in tbPriority, and tbStatus and tbResolType contain the different statuses and resolution types respectively. The tbSysParmType and tbSysSetup tables keep the system setup and configuration data.
Resolved requests are stored in tbReqHist. The details of the request are stored in tbReqDetHist. The text requests and responses are stored in tbTxtReqHist and tbTxtRespHist. Similarly, the voice requests and responses are stored in tbVoiceReqHist and tbVoiceRespHist.
All actions performed by clients are carried out by stored procedures rather than through direct manipulation of the HelpDesk database objects.
If a user submits a request via the user client, the pcIns_Req stored procedure inserts the request into tbReq. If a user submits a request via e-mail or telephone request, the pcIns_Msg stored procedure inserts a record into the tbMsgIn table.
During the resolution cycle, a technician may have to respond to the user either for a clarification or for more information. Therefore, there can be a continuous interaction between the user and the technician involved in the resolution.
The stored procedures in Table 4 are called, during the resolution cycle, in response to actions taken at the client.
Table 4. Stored Procedures for User Requests
| Stored procedure name | Action |
| PcSendTextReq | Inserts a record into tbReqDet. Also inserts a record into tbTxtReq. |
| PcSendVoiceReq | Inserts a record into tbReqDet. Also inserts a record into tbVoiceReq. |
Similarly, the stored procedures in Table 5 are called when a technician responds to a user.
Table 5. Stored Procedures for Technician Responses
| Stored procedure name | Action |
| PcSendStdTextResp | Inserts a record into tbReqDet. |
| PcSendStdVoiceResp | Inserts a record into tbReqDet. |
| PcSendTextResp | Inserts a record into tbReqDet. Also inserts a record into tbTextResp. |
| PcSendVoiceResp | Inserts a record into tbReqDet. Also inserts a record into tbVoiceResp. |
Each request will have a status associated with it, and each request passes through different statuses in its resolution cycle. The request status can be any of the following:
Table 6 provides a list of stored procedures that change the status of a request.
Table 6. Store Procedures for Changing the Status of a Request
| Stored procedure name | Status from | Status to |
| PcClaim_Req | Unclaimed | Claimed |
| PcUnclaim_Req | Claimed | Unclaimed |
| PcCommence_Req | Claimed | In progress |
| PcSubmit_Req | In progress | Pending resolution |
| pcResConfirm | Pending | Resolved |
| pcResNotConfirm | Pending | In progress |
The other stored procedures are listed, in no particular order, in Table 7.
Table 7. Additional HelpDesk Stored Procedures
| Stored procedure name | Action |
| PcGet_ReqDet | Retrieves a request details record from tbReqDet, and the detail text from the appropriate table, given the PKId as parameter. Used to fill the RequestDetail collection of the Request object. |
| PcGet_Req | Retrieves a request record from tbReq table given the PKId as parameter. Used to fill the attributes of the Request object. |
| PcGet_Tech | Retrieves a technician record from tbTech given the PKId as parameter. Used to fill the attributes of the Technician object. |
| PcLvw_Locations | Retrieves the list of location records in tbLocation. Used to identify the location of a request. |
| PcLvw_Priority | Retrieves the list of priority records in tbPriority. Used to identify the priority of request. |
| PcLvw_ReqDets | Retrieves the list of request detail records from tbReqDet, and the detail text from the appropriate table, that belong to a request. The PKId of the request is passed as a parameter. Used to show the request details chronologically. |
| PcLvw_Requests | Retrieves the list of request records from tbReq that belong to a technician. The PKId of the technician is passed as a parameter. Used to list all requests associated with a technician. |
| PcLvw_Skills | Retrieves the list of skill records from tbSkill. Used to identify the skill sets of a technician. |
| PcLvw_Status | Retrieves the list of skill records from tbStatusl. Used to identify the status of a request. |
| PcLvw_Techs | Retrieves the list of technician records from tbTech. |
Unclaimed requests are continuously monitored and shuffled between different available technicians in order to optimize request resolution time. For an explanation of this automated task, see "Task Distribution in a Customer Service System."
The sample files for the HelpDesk database associated with this article contain batch files, which when run from a Windows command prompt, will automate this process. See readme.txt in the sample files for the full HelpDesk database setup instructions.
You can also install the database from the setup program files available with the Setup and Deployment instructions for the HelpDesk sample.