Bug Database: A Windows Application

Bug Database A Windows Application

In our earlier book, Programming ASP.NET, we created a web-based bug-tracking application. ADO.NET does such a good job of abstracting the data from the implementing technology that much of the design of that application can easily be ported to Windows programming; recreating the bug tracking database as a rich-client desktop application.

You do not have to have read Programming ASP.NET to follow this discussion, though if you have read it, many of its design considerations will be familiar.

To build an application to track software bugs, you'll need a form for entering bugs and a form for reviewing and editing bugs. To support this, you will design a relational database to hold the data about each bug. ADO.NET supports any database technology that has an ODBC driver (Access, Oracle, etc.), but this book focuses on SQL Server because many ADO.NET classes are optimized for Microsoft's chosen database technology.

19.1.1 Preliminary Design Considerations

Begin by thinking about the kinds of information you want to capture in the database and how that information will be used. You will allow any user of the system to create a bug report. You'll also want certain users (e.g., developers and a quality assurance team) to be able to update bug reports. Developers will want to be able to record progress in fixing a bug or to mark a bug as fixed. QA will want to check the fix and either close the bug or reopen it for further investigation. The original reporter of the bug will want to find out who is working on the bug and track progress.

One requirement imposed early in the design process is that the bug database ought to provide an "audit trail." If the bug is modified, you'll want to be able to say who modified it and when they did so. In fact, you'll want to track changes to the bug so you can generate a report like the excerpt shown in Example 19-1.

Example 19-1. Excerpt from a bug report

Bug 101 - System crashes on login
101.1 - Reporter: Osborn 
Date: 1/1/2002 Original bug filed
Description: When I login I crash. 
Status: Open 
Owner: QA
 
101.2 - Modified by: Smith
Date: 1/2/2002 Changed Status, Owner
Action: Confirmed bug.
Status: Assigned
Owner: Hurwitz
 
101.3 - Modified by Hurwitz
Date 1/2/2002 Changed Status
Action: I'll look into this but I don't think it is my code.
Status: Accepted
Owner: Hurwitz
 
101.4 - Modified by Hurwitz
Date 1/3/2002 Changed Status, Owner
Action: Fault lies in login code. Reassigned to Liberty
Status: Assigned
Owner: Liberty 
 
101.5 - Modified by Liberty
Date: 1/3/2002 Changed Status
Action: Yup, this is mine.
Status: Accepted
Owner: Liberty
 
101.6 - Modified by Liberty
Date 1/4/2002 Changed Status, Owner
Action: Added test for null loginID in DoLogin( )
Status: Fixed
Owner: QA
 
101.7 - Modified by Smith
Date: 1/4/2002 Changed Status
Action: Tested and confirmed
Status: Closed
Owner: QA

To track this information, you'll need to know the date and time of each modification, as well as who made the modification and what was done. There will probably be other information you'll want to capture as well, though this may become more obvious as you build the application (and use it).

It is quickly becoming clear that you'll need two different tables to represent the bug itself. Each record in the Bug table will represent a single bug, but you'll need an additional table to track the revisions. Call this second table BugHistory.

A Bug record will have a bugID and include the information that is constant for the bug throughout its history. A BugHistory record will have the information specific to each revision.

19.1.2 The Bug Database Design

The bug database design described in this section includes three significant tables: Bugs, BugHistory, and People. Bugs and BugHistory work together to track the progress of a bug. For any given bug, a single record is created in the Bugs table, and a record is created in BugHistory each time the bug is revised. The People table tracks the developers, QA, and other personnel who might be referred to in a Bug report.

This simplified design meets the detailed specifications but focuses on key technologies; a robust professional design would necessarily be more complex. The complete database design used in this book is shown in the Appendix. The Appendix also provides a crash course on relational database design.

Figure 19-1 shows a snapshot of the Bugs table, and Figure 19-2 shows a snapshot of the BugHistory table.

Figure 19-1. The Bug table

Figure 19-2. The BugHistory table

When a bug is first entered, a record is created in the Bug and BugHistory tables. Each time the bug is updated, a record is added to BugHistory. During the evolution of a bug, the status, severity, and owner of a bug may change, but the initial description and reporter will not. The items that are consistent for the entire life of the bug are in the Bugs table; those that are updated as the bug is fixed are in the BugHistory table.

The reporter, for example, is the ID of the person who reported the bug. This ID is unchanged for the life of the bug, so it is recorded in the Bugs table. The owner may be adjusted from time to time, so it is recorded in the BugHistory table. In both cases, however, what is actually recorded is just a personID, which acts as a foreign key into the People table. An excerpt from the People table is shown in Figure 19-3.

Figure 19-3. The People table

In addition to these three primary tables, a number of secondary tables serve as lookup tables. For example, lkStatus serves as a lookup table for the possible values of BugHistory's status column.

The format for all lookup tables (lkStatus, lkProduct, lkRoles, and lkSeverity) is the same: the ID followed by a text field. Each table will hold one row for each possible value. Figure 19-4 shows various lookup tables.

Figure 19-4. The lookup tables

Figure 19-5 illustrates the tables in their various relationships graphically.

Figure 19-5. The relationship among the tables

SQL server generated Figure 19-5. The Enterprise Manager has a diagramming tool used to create this diagram, though you can create similar diagrams right within some versions of Visual Studio .NET.