11.1 Bug Database Design

Imagine that you have been asked to create a tool to manage bugs for a large development effort. You will be supporting three developers who will work in C# and VB.NET, along with a user interface designer and a few quality control engineers. You would like your design to be reasonably flexible so that you can reapply your bug tracking application to future projects.

Your first decision is that you will create a web application. This has the great advantage that all the participants will be able to access the application from their home computers. Since the developers work off-site, this is almost a necessity. You will, of course, develop your web application in ASP.NET.

You imagine that there will be a web page for entering bugs, as well as a page for reviewing and editing bugs. To support this, you will need to design a relational database, and for a number of reasons beyond the scope of this book, you decide to create that database using SQL Server.

You begin by thinking about the kinds of information you want to capture in the database, and how that information will be used. You will want to allow any user of the system to create a bug report. You'll also want certain users (e.g., developers and QA) to update the bug reports. Developers will want to be able to record progress in fixing a bug, or to mark a bug 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 be able to track all the changes to the bug, so that you can generate a report like the excerpt shown in Example 11-1.

Example 11-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 they did. 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 as you use it!).

One way to meet these requirements is to create two tables to represent each Bug. Each record in the Bugs table will represent a single bug, but you'll need an additional table to keep track of the revisions. Call this second table BugHistory.

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

The bug database design described in this chapter 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 in any way. The People table tracks the developers, QA, and other personnel who might be referred to in a Bug report.

This is a simplified design that meets the detailed specifications but which focuses on the key technologies; a robust professional design would necessarily be more complex. The complete database design as used in this book is shown in Appendix B. Script files and a backup to re-create the database are included with the downloadable sample code. A crash course on relational database design is provided in Appendix A.

Figure 11-1 shows a snapshot of the Bugs table, while Figure 11-2 shows a snapshot of the BugHistory table.

Figure 11-1. The Bugs table

Figure 11-2. The BugHistory table

When a bug is first entered, a record is created in each of the Bugs 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. Those items that are consistent for the entire bug are in the Bugs table; those that are updated as the bug is corrected are in the BugHistory table.

The reporter, for example, is the ID of the person who reported the bug. This is unchanged for the life of the bug and so is recorded in the Bugs table. The owner may be adjusted from time to time, and so 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 11-3.

Figure 11-3. The People table

In addition to these three primary tables, there are a number of secondary tables that serve as look-up tables. For example, lkStatus, serves as a look-up table for the possible values of the status column in BugHistory.

The format for all of the look-up 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. As an example, Figure 11-4 shows the various look-up tables.

Figure 11-4. The look-up tables

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

Figure 11-5. The relationship among the tables diagrammed