Feature

Building software systems is a lengthy and complicated process that may involve anywhere from a single developer to multiple teams of developers, who may all be working in different locations."

Many projects include complicating factors, such as the integration of diverse technologies, interaction with other software systems, or adherence to a defined set of requirements.

Is it any surprise that the software development process is prone to errors? Communication errors. Software defects (bugs). Or maybe even just ideas for features to be implemented in the next phase of a project (that tend to get lost or forgotten if they're not recorded somehow). These types of issues need to be tracked and resolved for any project of significant size.

The Web provides an excellent platform in which to field an application that will allow teams of developers to collaborate on tracking and resolving software issues. I will provide a design for a rudimentary Web-based issue-tracking system, as well as suggestions on potential enhancements to the system.

Goals of the System
The goals of this issue-tracking system are the following:

1. To serve as a central location where all project issues are recorded.
2. To allow developers, testers, and other personnel to collaborate in an essential development task, the resolution of project issues.
3. To serve as a record of issues that have been resolved.

To meet these goals, the system will record issues in a database, which will be Microsoft SQL Server in this case (the design could easily be adapted for other databases). The system design will support multiple projects, each of which may have numerous issues associated with it.

The system will provide a top-level Project page, which will display a list of projects to which a user has been assigned. Each project can have issues associated with it. The system will allow users to view, create, edit, and delete issues as needed.

Database Design
The foundation for this issue-tracking system is the database design, shown in Figure 1. As a convention, each table name begins with a prefix of "pt_", which means that these tables can generally be dropped into an existing database without producing a name conflict with existing tables.

The PT_PROJECT table provides a list of projects, each with a unique ID and a project name. This table also has a column called ACTIVE_IND (with possible values of Y or N) that indicates whether a project is active or not. This allows completed projects to be marked as inactive, so they don't appear on a user's list of projects.

Each project can have multiple issues associated with it. These issues are stored in the PT_ISSUES table, which contains the following columns:

• issue_id: A unique ID for the issue.
• change_date: The date and time that the issue is updated.
• entry_date: The date and time that the issue is first entered.
• issue_desc: A description of the problem. This can be of any length.
• issue_status_id: A foreign key to the PT_ISSUE_STATUS_LOOKUP table. An issue can have an issue status ID of "Open," "Test," or "Closed."

• issue_title: A short title to describe the issue.
• issue_type_id: A foreign key to the PT_ISSUE_TYPE_LOOKUP table. Issue types may include "Defect," "Idea," "Database Change," or any other types that may be appropriate.
• project_id: A foreign key to the PT_PROJECTS table. Stores the project ID of the project to which the issue belongs.
• priority_id: A foreign key to the PT_PRIORITY_LOOKUP table. Some issues are just more important than others. This column allows the importance of an issue to be defined, such as "Critical," "High," "Normal," "Low," and "Minor."

The PT_USERS table defines the users for the system. Then, the PT_PROJECT_USERS table defines the projects to which a user has access.

Let the Tracking Begin
The top-level Web page for the system is the Projects page, which displays a list of active projects to which a user has access. For this example system, the user ID is defined in the session variable, session.user_id. Typically, you would implement a login scheme for the system. Upon successfully logging in, the user ID would be set in a session variable, and would thus be available for the Projects page. Since we're focusing on the issue-tracking aspects of the system, I've provided a snippet of test code at the beginning of the Projects page that hard-codes the number 1 for the user ID. (The code for this article can be found at www.sys-con.com/coldfusion/sourcec.cfm.)

The Projects page is displayed in Figure 2, and the corresponding code is shown in Listing 1. The name of each project is set up as a hyperlink to the Issues page, with a URL that includes the project ID as a parameter.

The Issues page (see Figure 3) displays a concise and ordered list of issues for the specified project. Each row shows the title of an issue, the issue ID, the issue status, the priority and the last change date for the issue. The title of the issue is a link to the Issue View page, which displays all of the details concerning the issue. Each row also includes links to the Edit Issue and Delete Issue pages. Finally, near the top of the page, there's a link to the New Issue page.

Modifying Issue Information
These last three Web pages, the New Issue, Edit Issue, and Delete Issue pages, allow users to change information in the database after responding to a form. In each case, the form posts the information from the form to the same Web page. Thus, each Web page works in two modes.

In the first mode, no form information has been posted to the page. Therefore, the code displays the Web page with the form to collect information from the user. Upon submission, the information from the form is posted to that Web page, where ColdFusion makes them available as form variables.

The code checks to see if one of the form variables is defined. If it is, then the page goes into the second mode, handling the posted information. In the case of the New Issue page, for example, this means inserting information into the database. It uses the CFTRANSACTION tag to ensure that the operation, if it consisted of multiple steps, does not get interrupted. It then redirects the user back to the Issues page, properly specifying the URL parameter for the project.

This technique allows all code associated with a defined task, such as editing issue information, to be contained in a single source file. Some developers prefer not to handle forms this way, but it eases maintenance on those large ColdFusion projects.

Just a Starting Point
While useful, the issue-tracking system described in this article is a fairly basic example of the breed. Here are examples of enhancements that can be added to the system to create a more full-featured software development tool:

• Login System: Mentioned previously, the issue-tracking system almost demands a login mechanism.
• Privileges: A system for privileges could be created, so that not all users would have access to features, such as creating, editing, or deleting issues. For example, a consulting company might want to make the issue-tracking system available for a client, but set privileges so that only the development team could modify the issues.
• Security: Currently, a user could look at issues from a project they're not associated with simply by altering the project ID parameter in the URL for the Issues page. A more robust system would have some security in place to prevent this.
• Issue Notes: The system allows an issue to be updated with additional text to describe the resolution process. Many systems store updates as time-stamped notes that are associated with an issue. So an issue might consist of the original issue as entered, plus a series of entries in a PT_NOTES table that would represent a history of the issue and the steps taken to resolve it.

This is just a starting point. There are numerous features that could be added to expand the capabilities of this system.

Universal Accessibility
In only the past few years, the Web has expanded to the point where almost anybody can access the Web no matter where they are. Its near universal accessibility makes it the ideal platform for fielding collaborative Web applications, of which the issue-tracking system described in this article is only one example.