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.