Simple Bug Tracker – Database Structure

This is the second in a series of posts about the creation of a simple bug tracking system. The start page can be found here.

MySQL Database Creation

By using MySQL Workbench I created a new locally hosted database, namely ‘Bug Tracker’ and ran the following scripts to create the 4 tables previously documented.


CREATE TABLE Area
(
 Id int not null auto_increment,
 Area char(50) not null,
 PRIMARY KEY(Id)
) ENGINE=InnoDB;

CREATE TABLE UserDetails
(
 Id int not null auto_increment,
 Username char(50) not null,
 PRIMARY KEY(Id)
) ENGINE=InnoDB;

CREATE TABLE BugStatus
(
 Id int not null auto_increment,
 Status char(50) not null,
 PRIMARY KEY(Id)
);

CREATE TABLE `bugs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `areaId` int(11) NOT NULL,
 `ownerId` int(11) NOT NULL,
 `statusId` int(11) NOT NULL,
 `description` varchar(2500) NOT NULL,
 `fixDescription` varchar(2500) DEFAULT NULL,
 `dateRaised` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `dateClosed` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_area` (`areaId`),
 KEY `fk_owner` (`ownerId`),
 KEY `fk_status` (`statusId`),
 CONSTRAINT `fk_area` FOREIGN KEY (`areaId`) REFERENCES `area` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_owner` FOREIGN KEY (`ownerId`) REFERENCES `userdetails` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_status` FOREIGN KEY (`statusId`) REFERENCES `bugstatus` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

The Entity Relational Diagram is as follows:

Entity Diagram for Bug Tracker Database

This hopefully puts us in the right place for generating a GUI new week, before finally getting to implement some Java code.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s