14 How to Design an Issue Tracking Application

This tutorial describes how to plan, design and populate data objects for an example Issue Tracking application. After completing this tutorial, you can go on to Chapter 15, "How to Build and Deploy an Issue Tracking Application" to implement and deploy the application user interface to the data objects designed in this chapter.

A completed sample Issue Tracker application and supporting scripts are available on the Oracle Technology Network. Go to the following location and navigate to Packaged Applications and then select Issue Tracker:

http://www.oracle.com/technology/products/database/application_express/index.html

Note:

This tutorial takes approximately one to two hours to complete. It is recommended that you read through the entire tutorial first to become familiar with the material before you attempt specific exercises.

Topics in this section include:

Planning and Project Analysis
Designing the Database Objects
Implementing Database Objects
Loading Demonstration Data

For additional examples on this topic, please visit the following Oracle by Examples (OBEs):

Manipulating Database Objects

http://www.oracle.com/technology/obe/hol08/apexintro/apex3.1.1_a/apex3.1.1_a_otn.htm

Planning and Project Analysis

Effective project management is the key to completing any project on time and within budget. Within every project there are always multiple issues that need to be tracked, prioritized, and managed.

In this business scenario, MRVL Company has several projects that must be completed on time for the company to be profitable. Any missed project deadline will result in lost revenue. The company's project leads use various methods to track issues, including manually recording statuses in notebooks, organizing issues in text documents, and categorizing issues by using spreadsheets.

By creating a hosted application in Oracle Application Express, project leads can easily record and track issues in one central location. This approach offers each project lead access to just the data they need and makes it easier for management to determine if critical issues are being addressed.

Planning and Project Analysis

Before beginning development on an Oracle Application Express application, you first need to define application requirements. Then, you use the defined requirements to design a database and an outline that describes how the user interface accepts and presents data.

For this business scenario, the project leads establish requirements that define the information that must be tracked, security requirements, data management functions, and how to present data to users.

Topics in this section include:

Gather the Necessary Data
Define Security Requirements
Select Data Management Functions
Select Data Presentation Functions
Define Special Function Requirements

Gather the Necessary Data

Currently, each project lead tracks information slightly differently. Together, everyone agrees that the application should include the following information:

Summary of the issue
Detailed description of the issue
Who identified the issue
The date on which the issue was identified
Which project the issue is related to
Who the issue is assigned to
A current status of the issue
Priority of the issue
Target resolution date
Actual resolution date
Progress report
Resolution summary

Define Security Requirements

Because the project leads are concerned about everyone having access to all the information, they agree upon the following access rules:

Each team member and project lead is only assigned to one project at a time
Each team member and project lead must be assigned to a project
Managers are never assigned to a specific project
Only managers can define and maintain projects and people
Everyone can enter new issues
Once assigned, only the person assigned or a project lead can change data about the issue
Management needs views that summarize the data without access to specific issue details

Select Data Management Functions

Next, the project leads determine how information will be entered into the system. For this project, users must be able to:

Create issues
Assign issues
Edit issues
Create projects
Maintain projects
Create people
Maintain people information
Maintain project assignments

Select Data Presentation Functions

Once the data is entered into the application, users need to view the data. The team decides that users must be able to view the following:

All issues by project
Open issues by project
Overdue issues, by project and for all
Recently opened issues
Unassigned issues
Summary of issues by project, for managers
Resolved issues by month identified
Issue resolution dates displayed on a calendar
Days to Resolve Issues by person

Define Special Function Requirements

Finally, the project leads determine that the application must support the following special functions:

Notify people when an issue is assigned to them
Notify the project lead when any issue becomes overdue

Designing the Database Objects

Once you have defined the database requirements, the next step is to turn these requirements into a database design and an outline that describes how the user interface accepts and presents data. In this step you need to think about how information should be organized in the tables in the underlying database. Given the requirements described "Planning and Project Analysis", for this project you need to create three tables:

Projects tracks all current projects
People contains information about who can be assigned to handle issues
Issues tracks all information about an issue, including the project to which it is related and the person assigned to the issue

In addition to the tables, you also need to create additional database objects, such as sequences and triggers, to support the tables. System generated primary keys will be used for all tables so that all the data can be edited without executing a cascade update.

The data model designed for this exercise will look like Figure 14-1.

Figure 14-1 Data Model for Issue Tracker Database Objects

Description of "Figure 14-1 Data Model for Issue Tracker Database Objects"

Topics in this section include:

About the Projects Table
About the People Table
About the Issues Table

About the Projects Table

Each project must include project name, project start date, target date, and actual end date columns. These date columns help determine if any outstanding issues are jeopardizing the project end date. Table 14-1 describes the columns to be included in the Projects table.

Table 14-1 Project Table Details

Column Name	Type	Size	Not Null?	Constraints	Description
project_id	number	n/a	Yes	Primary key	A unique numeric identification for each project. Populated by a sequence using a trigger.
project_name	varchar2	255	Yes	Unique key	A unique alphanumeric name for the project.
start_date	date	n/a	Yes	None	The project start date.
target_end_date	date	n/a	Yes	None	The targeted project end date.
actual_end_date	date	n/a	No	None	The actual end date.
created_on	date	n/a	Yes	None	Date the record was created.
created_by	varchar2	255	Yes	None	The user who created the record.
modified_on	date	n/a	Yes	None	The date the record was last modified.
modified_by	varchar2	255	Yes	None	The user who last modified the record.

About the People Table

Each person will have a defined name and role. Project leads and team members will also have an assigned project. To tie the current user to their role within the organization, email addresses will be used for user names.

In order to associate the current user to a person, a username column will be added to the people table. This allows flexibility when deciding on the authentication mechanism and also allows for an authorization scheme that can determine who the person is that has logged on and if they have access to the application.

As a standard, add audit columns to each table. They do not need to be identified during analysis because they are added consistently to each table just before implementation.

Table 14-2 describes the columns that will be included in the People table.

Table 14-2 People Table Details

Column Name	Type	Size	Not Null?	Constraints	Description
person_id	number	n/a	Yes	Primary key	A numeric ID that identifies each user. Populated by a sequence using a trigger.
person_name	varchar2	255	Yes	Unique key	A unique name that identifies each user.
person_email	varchar2	255	Yes	None	User email address.
person_role	varchar2	30	Yes	Check constraint	The role assigned to each user.
username	varchar2	255	Yes	Unique Key	The username of this person. Used to link login to person's details.
assigned_project	number	n/a	No	None	The project this person is assigned to.
created_on	date	n/a	Yes	None	Date the record was created.
created_by	varchar2	255	Yes	None	The user who created the record.
modified_on	date	n/a	Yes	None	The date the record was last modified.
modified_by	varchar2	255	Yes	None	The user who last modified the record.

Note:

For the purposes of this exercise, this application has been simplified. User data is usually much more elaborate and is often pulled from a corporate Human Resource system. Also, users typically work on more than one project at a time. If the roles that are assigned to a user need to be dynamic, you would implement roles as a separate table with a foreign key that relates to the people table.

About the Issues Table

When the project leads defined their application requirements, they decided to track separate issues assigned to each person. Issues will be included in columns along with additional columns to provide an audit trail. The audit trail will track who created the issue, when it was created, as well as who modified the issue last and on what date that modification was made.

Table 14-3 describes the columns to be included in the Issues table.

Table 14-3 Issue Table Details

Column Name	Type	Size	Not Null?	Constraints	Description
issue_id	number	n/a	Yes	primary key	A unique numeric ID that identifies an issue. Populated by a sequence using a trigger.
issue_summary	varchar2	255	Yes	None	A brief summary of the issue.
issue_description	varchar2	4000	No	None	A detailed description of the issue.
identified_by_person_id	number	n/a	Yes	foreign key to People	The user who identifies the issue.
identified_date	date	n/a	Yes	None	The date the issue was identified
related_project	number	n/a	Yes	foreign key to Projects	Project related to the issue.
assigned_to	integer	n/a	No	foreign key to eba_it_people	The person who owns this issue.
status	varchar2	30	Yes	check constraint	The issue status. Automatically set to `Open` when new and set to `Closed` when actual resolution date entered.
priority	varchar2	30	No	check constraint	The priority of the issue.
target_resolution_date	date	n/a	No	None	The target resolution date.
progress	varchar2	4000	No	None	The progress of the issue.
actual_resolution_date	date	n/a	No	None	Actual resolution date of the issue.
resolution_summary	varchar2	4000	No	None	Resolution summary.
created_on	date	n/a	Yes	None	Date the record was created.
created_by	varchar2	255	Yes	None	The user who created the record.
modified_on	date	n/a	Yes	None	The date the record was last modified.
modified_by	varchar2	255	Yes	None	The user who last modified the record.

Note:

A real-world application might need more extensive auditing. For example, you might need to track each change to the data rather than just the last change. Tracking each change to the data would require an additional table, linked to the issues table. If the valid priorities assigned to issues need to be dynamic, you would be required to add a separate table with a foreign key that relates to the issues table.

Implementing Database Objects

This first step in building an application is to create the database objects.

Topics in this section include:

Additional Database Objects Needed
About Building Database Objects
Create and Run a Script to Build Database Objects
View the Created Database Objects

Additional Database Objects Needed

To populate the primary key values of the tables needed for this application, a sequence can be used. Another method is to create a function to provide a unique value. The latter method is used for this application. The function is created as part of a package named for the application. During implementation of the user interface, additional functions and procedures may be needed; they can be added to this one.

The DDL for the package specification and body is shown below. The package specification is created first, followed by the package body. The package body is created last because the body usually refers to tables that must already be specified. In this example, however, the body has no references to the tables.

About Building Database Objects

There are several ways to create objects in Oracle Application Express. You can:

Create an Object in Object Browser. Use Object Browser to create tables, views, indexes, sequences, types, packages, procedures, functions, triggers database links, materialized views, and synonyms. A wizard walks you through the choices necessary to create the selected database object. To create an object in Object Browser, navigate to SQL Workshop, then Object Browser, and click Create. See "Managing Database Objects with Object Browser" in Oracle Database Application Express User's Guide.
Execute SQL Commands. Run SQL Commands by typing or pasting them into the SQL Commands. To access SQL Commands, click the SQL Workshop icon on Workspace home page and then click SQL Commands. See "Using SQL Commands" in Oracle Database Application Express User's Guide.
Upload a script. Upload a script to the SQL Script Repository that contains all the necessary create object statements. To upload a script, click SQL Workshop on the Workspace home page, click SQL Scripts and then click Upload. See "Uploading a SQL Script" in Oracle Database Application Express User's Guide.
Create script online. Create a script online in the Script Repository. You will use this method to create database objects for this exercise. To create a script online, click the SQL Workshop icon on the Workspace home page, select SQL Scripts and then click Create. See "Creating a SQL Script in the Script Editor" in Oracle Database Application Express User's Guide.

For this exercise, you create and run a script.

Create and Run a Script to Build Database Objects

To build database objects by creating a script:

Log in to Oracle Application Express.
On the Workspace home page, click SQL Workshop and then SQL Scripts.
Click Create.
In the Script Editor:
1. For Script Name, enter DDL for Issue Tracker Application.
2. Copy the data definition language (DDL) in "Creating Application Database Objects DDL" and paste it into the script.
3. Click Save.
On the SQL Scripts page, click the DDL for Issue Tracker Application icon.

The Script Editor appears.
Click Run.

A summary page appears with a confirmation request.
Click Run again to confirm.

The Manage Script Results page displays a message that the script has been submitted for execution.

View the Created Database Objects

You can view database objects using Object Browser.

To view database objects in Object Browser:

Return to the Workspace home page. Click the Home breadcrumb link.
On the Workspace home page, click SQL Workshop and then Object Browser.
From the Object list on the left side of the page, select Tables.
To view the details of a specific object, select one of the following tables:
- IT_ISSUES
- IT_PEOPLE
- IT_PROJECTS
The tables will appear similar to those shown in Figure 14-2, Figure 14-3 and Figure 14-4.

Figure 14-2 IT_ISSUES Table

Description of "Figure 14-2 IT_ISSUES Table"

Figure 14-3 IT_PEOPLE Table

Description of "Figure 14-3 IT_PEOPLE Table"

Figure 14-4 IT_PROJECTS Table

Description of "Figure 14-4 IT_PROJECTS Table"

Loading Demonstration Data

Once you have created all the necessary database objects, the next step is to load data into the tables. You can manually load data or write and execute a script using the import functionality available in SQL Scripts. In the following exercise, however, you use SQL Scripts to load demonstration data. To allow the demonstration data to be created, removed and created again, the creation scripts have been wrapped into a package. You will first load and execute a script that will create the package specification and package body. You will then use the Command Processor to execute the procedures from within that new package.

To load demonstration data:

Click the SQL Workshop breadcrumb link.
Click SQL Scripts.
Click Create.
In the Script Editor, specify the following:
1. Script Name - Enter Load Data.
2. Copy the script in "Creating Issues Script" and paste it into the script.
3. Click Save.
On the SQL Scripts page, click the Load Data icon.

The Script Editor appears.
Click Run.

A summary page appears.
Click Run again.

The Run Script page displays with a request to confirm.
Click Run to confirm.

The Mange Script Results page displays a message that the script has been submitted for execution.
Click the SQL Workshop breadcrumb link.
Click SQL Commands.

To execute the procedures that load data into each table, enter the following:

begin
    it_sample_data.create_sample_projects;
    it_sample_data.create_sample_people;
    it_sample_data.create_sample_issues;
end;

Click Run.

All the Issue Tracker Application objects have been designed, created and populated with data. Now, you can continue on and complete Chapter 15, "How to Build and Deploy an Issue Tracking Application" to create the User Interface to these objects.