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 |
Foregin 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.). |
|
|
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 |
|---|---|
|
The bug has been reported but not yet assigned to anyone to fix. | |
|
Assigned (typically to a developer) but not yet accepted by that developer. | |
|
Accepted (typically by a developer) but not yet resolved. | |
|
Not Yet Deployed: the developer thinks he has fixed it but has not yet deployed it for testing. | |
|
Not A Bug: the developer alleges that this is the intended behavior or is otherwise to specification. | |
|
Not Reproducible: the developer cannot reproduce the behavior. | |
|
No Plan To Fix: the developer agrees that the bug is as shown, but believes that the organization ought not fix it. | |
|
Without commenting on whether the bug is real or not, the developer suggests deferring all future action on the bug. | |
|
The developer has deployed a fix for the bug but it has not yet been closed by QA. | |
|
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. |
With these tables, you are able to create a working bug database.
Any two tables will typically have one of the following relationships:
- One to Many
- Many to One
The reciprocal relationship of one to many. The developer is in a one-to-many relationship with bugs, and bugs are in a many-to-one relationship with developers
- Many to Many
You can imagine a system that allows more than one person to own a bug. Perhaps the bug is owned by a developer and also a marketing person. Each of these people may also own more than one bug. Thus, bugs and people would be in a many-to-many relationship. In the current design, we do not allow this relationship between bugs and people.
The relationships among the tables is shown in Table B-6.
Table B-6. The relationship among the tables
|
Primary |
Key |
Foreign |
Key |
Relationship |
|---|---|---|---|---|
|
BugHistory |
BugID |
Bugs |
BugID |
One bug to many history records. |
|
BugHistory |
Status |
lkStatus |
StatusID |
Each BugHistory has one status. |
|
BugHistory |
Severity |
lkSeverity |
SeverityID |
Each BugHistory has one severity. |
|
BugHisotry |
Owner |
People |
PersonID |
Each BugHistory has one owner. |
|
Bug |
Reporter |
People |
PersonID |
Each Bug has one Reporter. |
|
Bug |
Product |
lkProduct |
ProductID |
Each Bug has one Product. |
|
People |
Role |
lkRoles |
RoleID |
Each person has one Role. |