Appendix B. Bug Database Architecture

The Bug database consists of three primary tables (Bugs, BugHistory, and People) and four secondary (lookup) tables (lkProduct, lkRoles, lkSeverity, and lkStatus). To keep the examples as simple as possible, these tables have been stripped down to the absolute essentials.

Table B-1 shows the structure of the Bugs table and Table B-2 shows the structure of the BugHistory table. Table B-3 shows the structure of the People table.

Table B-1. Structure of the Bugs table

Column	Type	Notes
BugID	int (identity)	Uniquely identifies each bug in the database.
Product	int	Foreign key into lkProducts (identifies the unique product).
Version	varchar	Text description of the version number (e.g., 0.1.2).
Description	varchar	Text description of the bug as entered by the person recording the bug.
Reporter	int	Foreign key into People (ID of the person reporting the bug).

Table B-2. Structure of the BugHistory table

Column	Type	Notes
BugHistoryID	int (identity)	Uniquely identifies each bug history record.
BugID	int	Foreign key into Bugs table. This column combined with BugHistoryID is sufficient to track all the updates for a given bug.
Status	int	Foreign key into lkStatus—identifies the current status of the bug (e.g., open, closed, etc.).
Severity	int	Foreign key into lkSeverity—identifies the current severity of the bug (e.g., high, medium, low, etc.).
Response	varchar	Text description of the action taken at this step in the progress of the bug resolution.
Owner	int	Foreign key into People — identifies the current "owner" of the bug (typically a developer while bug is unresolved).
DateStamp	datetime	Date and time stamp for the current entry.

Table B-3. People

Column	Type	Notes
PersonID	int (identity)	Uniquely identifies each person's record.
FullName	varchar	Text of person's full name (title, first, last and suffix: e.g., Mr. John Galt, Jr.).
eMail	varchar	Optional text field for email address. Cannot be null, can be blank.
Phone	varchar	Optional text field for telephone number. Cannot be null, can be blank.
Role	int	Foreign key into lkRoles, designates the person's current role within the organization (e.g., QA, Developer, etc.).

Each record in the BugHistory table has a Status value. The possible values for the Status field are captured in lkStatus, as shown in Table B-4.

Table B-4. Possible status values

Status	Notes
Open	The bug has been reported but not yet assigned to anyone to fix.
Assigned	Assigned (typically to a developer) but not yet accepted by that developer.
Accepted	Accepted (typically by a developer) but not yet resolved.
NYD	Not Yet Deployed: the developer thinks he has fixed it but has not yet deployed it for testing.
NAB	Not A Bug: the developer alleges that this is the intended behavior or is otherwise to specification.
NR	Not Reproducible: the developer cannot reproduce the behavior.
NPTF	No Plan To Fix: the developer agrees that the bug is as shown, but believes that the organization ought not fix it.
Defer	Without commenting on whether the bug is real or not, the developer suggests deferring all future action on the bug.
Fixed	The developer has deployed a fix for the bug but it has not yet been closed by QA.
Closed	Closed by QA.

You can certainly imagine other status values, but these will get you started. The work flow envisioned is that a bug is reported by entering the bug in the system. QA reads through the Open bugs and assigns a bug to a developer. The developer asks for all the bugs with his ID as owner and the status of Assigned and accepts the bugs. He then works on the bugs and marks them one of NYD, NAB, NR, NPTF, Defer, or (ideally) Fixed. QA then checks the results and either resets the status (e.g., marks a bug from Defer back to Assigned), reassigns the bug, or marks it Closed.

Table B-5 illustrates the lkStatus table structure.

Table B-5. lkStatus

Column	Type	Notes
StatusID	int (identity)	Uniquely identifies each status record.
StatusDescription	VarChar	One of the values shown in the left column in Table B-4.