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.
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.
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.
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-5 illustrates the tables in their various relationships graphically.