The 3T database
The following SQL statements define the MySQL tables used in 3T. It is possible to use any database, and MySQL-specific code is highlighted to identify differences with ANSI SQL.
The company table
A company has projects for which we need to keep track of the time spent on different tasks. The company is, hence, the first table that needs to be defined. It is a very simple structure:
create table ttt_company( id_company int unsigned not null auto_increment, company_name varchar(200) not null, primary key(id_company) );
The auto_increment keyword is used by MySQL to identify a number column that should automatically be incremental (the default rate of increment is by one number) based on the current highest value in the column. This is used to generate the id_company primary key values. Let's add some company data:
insert into ttt_company(company_name) values ('PACKT Publishing');
insert into ttt_company(company_name) values ('Gieman It Solutions');
insert into ttt_company(company_name) values ('Serious WebDev');
After entering these statements into the SQL Command editor, you can execute the statements by clicking on the button in the top-right corner of the following screenshot (the Run SQL button is circled):
The output of these statements will be shown at the bottom of the IDE:
You can now view the inserted data by executing the following statement in the SQL Command editor:
select * from ttt_company;
Alternatively, you can also right-click on the table node in the databases and select View Data…:
This will result in the following screenshot:
The projects table
A company may have any number of projects with each project belonging to exactly one company. The table definition is as follows:
create table ttt_project( id_project int unsigned not null auto_increment, project_name varchar(200) not null, id_company int unsigned not null, primary key(id_project), foreign key(id_company) references ttt_company(id_company) );
Once again, we can add some data:
insert into ttt_project(project_name, id_company) values('Enterprise Application Development with Spring and ExtJS', 1);
insert into ttt_project(project_name, id_company) values ('TheSpring Framework for Beginners', 1);
insert into ttt_project(project_name, id_company) values('Advanced Sencha ExtJS4 ', 1);
insert into ttt_project(project_name, id_company) values ('The 3TProject', 2);
insert into ttt_project(project_name, id_company) values('Breezing', 2);
insert into ttt_project(project_name, id_company) values ('GiemanWebsite', 2);
insert into ttt_project(project_name, id_company) values('Internal Office Projects', 3);
insert into ttt_project(project_name, id_company) values('External Consulting Tasks', 3);
In these insert statements, we have provided the foreign key to the company table and once again allowed MySQL to generate the primary keys. Executing these commands and browsing the ttt_project table data should be displayed as shown in the following screenshot:
The tasks table
A project may have any number of tasks with each task belonging to exactly one project. The table and test data can now be added as follows:
create table ttt_task( id_task int unsigned not null auto_increment, id_project int unsigned not null, task_name varchar(200) not null, primary key(id_task), foreign key(id_project) references ttt_project(id_project) );
We will now add a range of tasks for some of our projects:
insert into ttt_task(id_project, task_name)values (1, 'Chapter 1'); insert into ttt_task(id_project, task_name)values (1, 'Chapter 2'); insert into ttt_task(id_project, task_name)values (1, 'Chapter 3'); insert into ttt_task(id_project, task_name)values (2, 'Chapter 1'); insert into ttt_task(id_project, task_name)values (2, 'Chapter 2'); insert into ttt_task(id_project, task_name)values (2, 'Chapter 3'); insert into ttt_task(id_project, task_name)values (3, 'Preface'); insert into ttt_task(id_project, task_name)values (3, 'Appendix'); insert into ttt_task(id_project, task_name)values (3, 'Illustrations'); insert into ttt_task(id_project, task_name)values (4, 'DatabaseDevelopment'); insert into ttt_task(id_project, task_name)values (4, 'Javadevelopment'); insert into ttt_task(id_project, task_name)values (4, 'SenchaDevcelopment'); insert into ttt_task(id_project, task_name)values (4, 'Testing');
Executing these commands and browsing the ttt_task table data will display the following screenshot:
The user table
The next table in our design holds user information:
create table ttt_user( username varchar(10) not null, first_name varchar(100) not null, last_name varchar(100) not null, email varchar(100) not null unique, password varchar(100) not null, admin_role char(1) not null, primary key(username) );
Note that the admin_role column will be used to identify if a user has administrative permissions in the 3T application. We will now add two users:
insert into ttt_user(username, first_name, last_name, email,password, admin_role) values ('jsmith', 'John', 'Smith', 'js@tttracker.com', 'admin','N');
insert into ttt_user(username, first_name, last_name, email,password, admin_role) values ('bjones', 'Betty', 'Jones', 'bj@tttracker.com','admin','Y');
Running this set of commands will create the user table and then insert our two test users as displayed in the following screenshot:
The task log table
The final table will be used to enter the time spent on different tasks.
create table ttt_task_log( id_task_log int unsigned not null auto_increment, id_task int unsigned not null, username varchar(10) not null, task_description varchar(2000) not null, task_log_date date not null, task_minutes int unsigned not null, primary key(id_task_log), foreign key(id_task) references ttt_task(id_task), foreign key(username) references ttt_user(username) );
We will now add some data to this table for our user John Smith (jsmith). Note that the time spent on each task is in minutes and that the MySQL function now() is used to return the current timestamp:
insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(1,'jsmith','Completed Chapter 1 proof reading',now(),120); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(2,'jsmith','Completed Chapter 2 draft',now(), 240); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(3,'jsmith','Completed preparation work for initialdraft',now(), 90); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(3,'jsmith','Prepared database for Ch3 task',now(), 180);
In a similar way, we will insert some test data for Betty Jones (bjones):
insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(1,'bjones','Started Chapter 1 ',now(), 340); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(2,'bjones','Finished Chapter 2 draft',now(), 140); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(3,'bjones','Initial draft work completed',now(), 450); insert into ttt_task_log (id_task, username, task_description,task_log_date,task_minutes)values(3,'bjones','Database design started',now(), 600);
The result of these insert statements can now be viewed as shown in the following screenshot:
