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:

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

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

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 follows
Description of "Figure 14-1 Data Model for Issue Tracker Database Objects"

Topics in this section include:

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

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:

  1. Log in to Oracle Application Express.

  2. On the Workspace home page, click SQL Workshop and then SQL Scripts.

  3. Click Create.

  4. 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.

  5. On the SQL Scripts page, click the DDL for Issue Tracker Application icon.

    The Script Editor appears.

  6. Click Run.

    A summary page appears with a confirmation request.

  7. 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:

  1. Return to the Workspace home page. Click the Home breadcrumb link.

  2. On the Workspace home page, click SQL Workshop and then Object Browser.

  3. From the Object list on the left side of the page, select Tables.

  4. 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 follows
    Description of "Figure 14-2 IT_ISSUES Table"

    Figure 14-3 IT_PEOPLE Table

    Description of Figure 14-3 follows
    Description of "Figure 14-3 IT_PEOPLE Table"

    Figure 14-4 IT_PROJECTS Table

    Description of Figure 14-4 follows
    Description of "Figure 14-4 IT_PROJECTS Table"

See Also:

"Managing Database Objects with Object Browser" in Oracle Database Application Express User's Guide.

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:

  1. Click the SQL Workshop breadcrumb link.

  2. Click SQL Scripts.

  3. Click Create.

  4. 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.

  5. On the SQL Scripts page, click the Load Data icon.

    The Script Editor appears.

  6. Click Run.

    A summary page appears.

  7. Click Run again.

    The Run Script page displays with a request to confirm.

  8. Click Run to confirm.

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

  9. Click the SQL Workshop breadcrumb link.

  10. Click SQL Commands.

  11. 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;

  12. 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.