You are here: HomeProjectsSeries 2 - Project and Product ManagementSeries 2 : Part 2 - A Practical Database Implementation of a Things To Do List

Series 2 : Part 2 - A Practical Database Implementation of a Things To Do List

Abstract


100024In this part of the series the two entities and their attributes, from the E-R Diagram developed in the previous part of the series, are used to define two database tables. Each tables, the things to do and note tables, are then created in the database by executing MYSQL statements from a file. Then finally, after each table is populated with data from a text file,  a method of creating a composite key from the two table's primary keys, is used to create the relationship between the things to do and notes tables.  

DOWNLOADS are almost available and are on our things to do list.  See the Download section at the end of this document on how to download the data for Series 2 Part 2. Alternatively grab the code from Series 2 : Example Set 01

Introduction 

In the last article, in the series, we established the guidelines, or rules, for the things to do part of our database. This blueprint of our plans was developed as an E-R diagram. In this part of the series we will continue by providing a practical implementation of the database defined in the blueprint. The implementation will show how the E-R diagram's entities will be used as database tables and their attributes as the columns. It will also show how the relationship between the two entities, the things to do and the notes, can be represented as a third table, rather than reference one table from the other.

Creating the Database

MYSQL (5.5.31-0) on LINUX (ubuntu 0.12.04.1) is our platform of choice for development of the  project and product engineering management database. However, from our humble experience, the variant of the SQL (Standard Query Language) used and the platform it's used on should be irrelevant to any coding decision undertaken. Most great craftsmen are noted for their work and not the tools used to create it. However, after saying all that, its worth noting that database table names in MYSQL are case sensitive in LINUX, which is not the case on the Windows platform!

Talking about great craftsmen we almost failed to get over the first hurdle, on this project, as we struggled to create a database! (LOL - Ed). Finally, by luck rather than better judgement we managed to get to the bottom of the issue, accidentally, by stumbling upon the correct procedure used to grant privileges to a user as can be seen in the MYSQL listing, in Listing 1, below. 

This solution may not work for everyone and in some cases it may require a systems administrator to wave a magic wand and unlock the required database privileges. Any security fears we may have had, in issuing this grant privileges command, were  quite quickly allayed by agreeing, initially, to use this database offline until we understand the command a bit better. 

create database blahexpblah;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'blahexpblah'

mysql --user=root mysql
grant all privileges on *.* to 'user_name'@'localhost' with grant options;

Listing 1:  A listing of the MYSQL code used to grant all privileges to a user.  (An alternative method used to login to mysql is in a terminal window type > mysql -u root -p).

If an eyebrow is raised at the database name, there is a reasonable explanation as to why the name seems so random. Nowadays, its not uncommon to name a database as unhelpfully as possible, for example blahexpblah, as in Listing 1, for security reasons. A meaningless name makes it harder for an intruder to zero in on one's valuable data, especially when a large number of databases are similarly named. Having to search through thousands of databases, irrationally named, could keep an intruder online longer than they would like to be, increasing their probability of getting caught or at least forcing them to leave a longer paper trail of their illicit activity.

Creating the ThingsToDo (TTD) Table

The task at hand requires us to create a database table of the ThingsToDo  entity, defined in  the previous article and repeated, in Figure 1, below for convenience.

 

0000503

Figure 1: Figure repeats the E-R diagram developed in Part 1 of the series.

The entity ThingsToDo (ttd) has four attributes one of which, date, is a complex attribute. The other three attributes are status, description and urgency. To define the characteristics of the entity it is sometimes easier to tabulate each attribute, its data type, default value and whether the field can be left empty (NULL) or not left empty (NOT NULL). This is exactly waht we have done and can be  seen, in Table 1, below.

Table 1: The Characteristics of the ThingsToDo Entity

Attribute Data Type NULL Default Value Remark
id INT(11) NOT NULL  Auto Increment  The unique identifier of the database. This will be the PRIMARY KEY.
description VARCHAR(255) NOT NULL

Restrict the description waffle to 255 characters, we may find out later on that 255 characters is inadequate. 

status BOOLEAN NOT NULL  0 Here, 0 represents OPEN and 1 represents CLOSED.
urgency ENUM NOT NULL  0 we could provide 5 priority levels and name them low (0), medium (1), high (2), very high (3), urgent (4).
insertdate TIMESTAMP NOT NULL  Now  We have decided to specify the date the data is inserted into the database in CCYY:MM:DD:hh:mm:ss format.

For the more observant, i.e those not finding reading this a cure for insomnia, it might have been noted that the MYSQL data types specified in Table 1 are in capital letters. This is not entirely necessary, but it has become somewhat of an unwritten convention to capitalise SQL keywords. The table appears to be quite self explanatory, although it might be worth noting the following. The unique identifiers (id) are automatically incremented by 1 on each new entry, unless a specific id value is provided when new data is inserted into the table. Also,  five levels of urgency have been anticipated, with 0 being the least urgent and 4 being the most urgent. One way of creating the Things To Do table, called ttd, is by executing the statements, in Listing 2, below. 

SELECT '<INFO_TO_DISPLAY>' AS ' ';

CREATE TABLE IF NOT EXISTS ttd 
(
       id INT(11) NOT NULL AUTO_INCREMENT,
       description VARCHAR(255) NOT NULL,
       status BOOLEAN NOT NULL DEFAULT 0,
       insertdate TIMESTAMP NOT NULL DEFAULT NOW(),
       PRIMARY KEY(id)
) ENGINE=InnoDB;

Listing 2: MYSQL code used to create the ttd (things to do) table. (see create_ttd.sql). Question What does the first statement do?

From our limited experience of developing databases we have noted that, in the early days  when experimenting with MYSQL, the biggest hindrance to rapid code development can be syntax errors, especially when creating tables and inserting data items. Hence, when the code in listing 2 is saved to a text file, create_ttd.sql for example, then as can be seen, in Listing 3, we could execute the MYSQL statements directly from the file. This could be considered to be quite a prudent way, for beginners, to execute MYSQL statements and avoid repetitive typing when syntax errors occur. 

source ./home/user_name/dir_path/file_name.sql

 Listing 3: Executing MYSQL statements from a file.

This method limits the errors to a single file and helps avoid the frustration of countlessly correcting them. To check the result of the ttd, things to do, table creation we could issue the MYSQL SHOW COLUMNS statement as demonstrated, in Listing 4, below.

SHOW COLUMNS FROM ttd;
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| description | varchar(255) | NO   |     | NULL              |                |
| status      | tinyint(1)   | NO   |     | 0                 |                |
| insertdate  | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+--------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

Listing 4: Issuing the SHOWS COLUMNS MYSQL statement to show the characteristics of an entity.

 Creating the Notes Table

To create the notes table we undergo a similar exercise  to the one undertaken above for the ttd, things to do table. Firstly, we tabulate the note entity's characteristics as can be seen, in Table 2, below.  

Table 2: The tabulated Notes table.

Attribute Data Type NULL Default Value Remark
id INT(11) NOT NULL  Auto Increment  The unique identifier of the database. This will be the PRIMARY KEY.
comment VARCHAR(255) NOT NULL

Restrict the waffle to 255 characters.

insertdate TIMESTAMP NOT NULL  Now Insert the date in CCYY:MM:DD:hh:mm:ss format.

From this defintion, of the notes table's characteristics, we  can execute the MYSQL statements required to create the table in the database as can be seen in, Listing 5, below.

SELECT '<INFO_TO_DISPLAY>' AS ' ';

CREATE TABLE IF NOT EXISTS notes
(
       id INT(11) NOT NULL AUTO_INCREMENT,
       comment VARCHAR(255) NOT NULL,
       insertdate TIMESTAMP NOT NULL DEFAULT NOW(),
       PRIMARY KEY(id)
) ENGINE=InnoDB;

 SHOW COLUMNS FROM notes;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| comment    | varchar(255) | NO   |     | NULL              |                |
| insertdate | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

Listing 5: MYSQL code used to create the notes table. (see create_notes.sql). 

Populating the Tables with Data Entries

When setting up a new database a preferred method of inserting data values into a table is to use a text file. The MYSQL statement shown, in Listing 5, below could be used for this very purpose.

LOAD DATA INFILE 'ttd_entries.txt, INTO TABLE blahexpblah.ttd 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Listing 6 : Shows how to load the ttd_entries.txt file, of a list of things to do, into our blahexpblah database.

An example contents of this file can be seen in the content's snippet below. MYSQL was not very happy with the status field set to NULL, so we explicitly set its value to 0 to indicate that the status is in the empty state.

Things To Do can have Notes

So far we have created two separate tables, a table of things to do (ttd) and a table of notes. In our implementation, so far, there is no physical relationship between the two. That is, we have not referenced (or linked) things to do (ttd), as a foreign key, in the notes table. We have done things this way for a very good reason. In our grand scheme of things, as we expand the database, others entities may also have notes (see below). Hence, we do not wish to alter the notes table with an additional foreign key each time we need to associate notes with a new entity.
 
Instead, to relate the two entities, ttd and notes, the method we prefer to use is to create a third table called, ttdnotes. In this table we create the association between the two tables by creating a composite key consisting of  the primary keys of the  ttd (id) and the notes (id) tables. Hence, in this new  table things to do have notes.
 
What happens if either of the entries associated with each other is deleted from the database? Well, the MYSQL statement, ON DELETE CASCADE, is used to delete entries from our new table if either the ttd table entry or the notes entry is deleted. Listing 7 shows how the ttdnotes table is created.
SELECT '<INFO_TO_DISPLAY>' AS ' ';

CREATE TABLE IF NOT EXISTS ttdnotes (
ttd INT(11) NOT NULL,
notes INT(11) NOT NULL,
PRIMARY KEY(ttd,notes),
FOREIGN KEY(ttd)
REFERENCES ttd(id)
ON DELETE CASCADE,
FOREIGN KEY(notes)
REFERENCES notes(id)
ON DELETE CASCADE
)ENGINE=InnoDB;

show columns from ttdnotes;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ttd   | int(11) | NO   | PRI | NULL    |       |
| notes | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Listing 7 : Shows how to relate the ttd and notes tables by using a composite key consisting of the primary keys from the two other  tables.
  

Conclusion

In this part of the series we have created a sparse, but instantly usable, database containing three tables. The first contains a list of things to do, ttd, and the seconds the notes that are attached to the things to do. There are probably many more columns that could be added to either table and no doubt columns may be added as the database develops. For now we have a working database to fulfill our urgent  requirement of coordinating our things to do list by using a database.
 
To expand our database we would like to add a project table, to keep track of our numerous projects including, project series and tutorials, as well as our software and FPGA firmware projects. Each project should have a list of things to do, tdd, which could have notes. Projects themselves could have notes. A preliminary  E-R Diagram  representing the increased complexity of our database can be seen, in Figure 2, below.
 
100024 
Figure 2: Figure shows a more complicated E-R Diagram incorporating the Project entity.

The addition of a project table, which will require writing a new problem statement and the means by which the increased complexity of the database is tackled, will be the focus of the next part of the series.
 

Downloads

filename Description
series2part2.tar.???? A tarred file containing all of the files below.
create_ttd.sql MYSQL commands used to create the ttd table.
create.notes.sql MYSQL commands used to create the notes table.
create_ttdnotes.sql MYSQL commands used to create the ttdnotes table.
ttd_entries.txt Test values inserted into the ttd table.
notes_entries.txt Test values inserted into the notes table.
ttdnotes_entries.txt Test values inserted into the ttdnotes table.
license.txt License governing code usage.


Go to comments start