Figure 3: List of tables and attributes

Table: Oticket

This is the master record table for the help desk tickets. Each ticket is one row.

Attributes:
ticketId                numeric(9,0) IDENTITY         Helpdesk ticket ID
submitDate              smalldatetime NULL            Date submitted
dateNeededBy            varchar(10) NULL              Date needed by
status                  varchar(1) NULL               A (assigned),
U (unassigned), C (closed)
department              varchar(30) NULL              Department Name
problemType             varchar(1) NULL               Problem Type *
requestDescription      varchar(255) NULL             Request Description (field 1)
requestDescription2     varchar(255) NULL             Request Description (field 2)
submitFirstName         varchar(64) NULL              Submitters first name *
submitLastName          varchar(64) NULL              Submitters last name *
priority                varchar(2) NULL               User assigned priority (1-5)
assigned                varchar(64) NULL              Support person's name *
eMail                   varchar(25) NULL              Submitter's email address
ownerId                 smallint NULL                 Foreign key to support
person's record
problemTypeId           smallint NULL                 Foreign key to problem type
record
submitterId             smallint NULL                 Foreign key to submitter's
employee id
* = used for version 1 compatibility.

Table: Cticket
This is the detail records table for the help desk tickets. There could be multiple rows for each help desk ticket.

Attributes:
ticketId                numeric(9,0) NOT NULL         Helpdesk ticket ID
occurance               numeric(9,0) IDENTITY         Item number for Helpdesk

ticket ID
resolution              varchar(255) NULL             Action description (field 1)
lastModifyDate          smalldatetime NULL            Date of action
assignedTo              varchar(64) NULL              Name of support person that
completed a task *
keyWords                varchar(80) NULL              Key words to search for **
statusChange            varchar(1) NULL               New status (A (assigned),
U (unassigned), C (closed)
resolution1             varchar(255) NULL             Action description (field 2)
timeSpent               smallmoney NULL               Minutes spent on a task
ownerId                 smallint NULL                 Foreign key to support
person's record

* = Used for version 1 compatibility.
** = Created for future use.

Table: Owner
This is a table that contains all of the necessary information for the support people.

Attributes:
ownerId                 smallint NOT NULL             Support person's Id
ownerName               varchar(25) NULL              Support person's name
ownerEmail              varchar(25) NULL              Support person's e-mail

Table: ProblemTypes
This table contains the information for the problems that you want to track.

Attributes:
problemTypeId           smallint NOT NULL             Problem type Id
typeDescription         varchar(20) NULL              Problem type description