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