DEV Community

Jimmy Gitonga
Jimmy Gitonga

Posted on • Updated on

Designing a database with MySQL using the Command Line Interface — Part 2

#mysql #webdev #programming

In the first part of Designing a database with MySQL using the Command Line Interface — Part 1, we have built a database and in the process used the most important queries in MySQL, namely, CREATE, USE, DESCRIBE, INSERT, SELECT, DELETE and ALTER.

We will continue to build out the Waks Noma database.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| waks_noma          |
+--------------------+
5 rows in set (0.01 sec)
Enter fullscreen mode Exit fullscreen mode

Then:

mysql> use waks_noma;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Enter fullscreen mode Exit fullscreen mode

We are ready.

Adding Categories Table: maskils

A good designer’s portfolio may have a number of categories: web development, motion graphics, branding, graphic design, and video production. Inexperienced database designers will just add a categories column (or columns) to next to the jobs so that data entry is of a job and its category. This is simple enough, but by adding a column called job_type, what would happen is that every so often, the type of work would be repeated or a project would have more than one job category, typically, like when making a multimedia presentation.

Unfortunately, this table is now inflexible. Because category names are repeated, any changes in spelling or wording will require more updating and are more likely to result in both system and user error.

Design decisions

Our categories are: Graphic Design, Web Design, Video production. Should we break this into sub categories, like under graphic design; brand identity, poster development, print layout? Can we list all the current and add future categories?

The decision is made to add a separate table of job categories:

/*** CREATE Categories table ***/
create table maskils (
  cat_id int not null auto_increment primary key,
  cat_name varchar(20) not null,
  cat_desc text not null
);
/*** end CREATE ***/
Enter fullscreen mode Exit fullscreen mode

We get:

mysql> show tables;
+---------------------+
| Tables_in_waks_noma |
+---------------------+
| majobo              |
| maskils             |
+---------------------+
2 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

We will load the data form a local file.

mysql> load data local infile '[absolute path/to/]madiambo.txt' into table maskils;
Query OK, 7 rows affected, 7 warnings (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0
Enter fullscreen mode Exit fullscreen mode

Our maskils table look thus:

mysql> select * from maskils;
+--------+--------------------+------------------------------------+| cat_id | cat_name           | cat_desc                                                                                                                                                                                            +--------+--------------------+------------------------------------+|      1 | Graphic Design     | This category deals single page design and layouts that include brand identities, posters, banners, T-shirt designs and so on.    |
|     2 | Print Layout        | This category includes magazine layouts brochures, posters, banners and brand collateral.          |
|      3 | Web Design         | This category describes User Interface design (UI) whether left at design or exploded to a working prototype. It also includes Information Architecture (IA) flows with the subsequent Wire Frames.                             |
|      4 | Web Development    | This category deals with the middleware used such as PHP or Content Management Solutions (CMS) such as Wordpress, Joomla, and Drupal or frameworks such as Ruby on Rails and Django.                                                  | 
|      5 | Video Production   | This category deals with video product development and delivery from script development to the finished product ready for delivery.                               |       |      6 | Motion Graphics    | This category has animated graphics for Television and film. It can incorporate web animation that enhances UX.                  |
|      7 | Animation          | This category deals with 2D and 3D animation, whether story based, character driven, web banners or motion graphics for insertion in TV and web content.               |
|      8 | Mobile Development | This category covers work singly or incorporating other media that is specifically geared for delivery on mobile handsets and smart phones.                               | +--------+--------------------+------------------------------------+8 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

The only purpose of this table is to hold information about categories. Ultimately, this design is simpler because each table makes sense on its own, independent of other tables. Each table will have one job and only one job. majobo will only hold information about the work done. maskils will only hold information about the categories of the work done.

maskils is structured as follows:

maskills ERD

By assigning only one job to each table, we can simplify the data and enable ourselves to better manage the data. For instance, if we need to rename the category "Video Production" to "Film", we now only need to update one row in maskils. If our categories were a column in the table majobo, we would have to go through the pain of searching every record in majobo for "Video Production" and updating each of those rows - an unnecessarily difficult process. In fact, all of our SQL statements will become easier to write and faster to process.

Assigning Jobs to Categories

If each table only has one job, then majobskils will be to link a specific job done to a specific category describing the type of work done. Thus, it will only hold two columns: job_id and cat_id. Both columns are called Foreign Keys. Columns designated as foreign keys will hold values that are primary keys in other tables. That is, job_id is a primary key in majobo. In majobskils, the column work_id is a foreign key. The same goes for cat_id and maskils.

/*** CREATE majobskils Table ***/
CREATE TABLE majobskils (
job_id INT NOT NULL,    
cat_id INT NOT NULL
)TYPE = myisam;
/*** end CREATE TABLE ***/
Enter fullscreen mode Exit fullscreen mode

NOTE: With the creation of this table, we have declared a type of MySQL storage engine. The main ones are InnoDB and MyISAM. The current general difference is that InnoDB allows transactions and MyISAM does not. MySQL 5.x uses InnoDB by default. For more information, investigate on MySQL storage engines.
For our majobskils, the table does not need to handle transactions since it carries data that will not change in the near future.

So comparing the two tables:

majobo 

+--------+-------------+-------------------------+-------------------+
| job_id | job_name    | job_desc            ...      |
+--------+-------------+-------------------------+-------------------+
|      1 | The Warehouse  | ... The web space was required  ... |
|      2 | Aim to Succeed | This was a graphic design poster ... |
|      3 | Oiling Life    | ... The brochure was to be ... |
|      4 | Dying to Give  | The main piece was a video documentary ...  |
+--------+-------------+-------------------------+-------------------+
Enter fullscreen mode Exit fullscreen mode

maskils 

+--------+--------------------+--------------------------------------+
| cat_id | cat_name           | cat_desc                                                                                                                                                                                    |
+--------+--------------------+--------------------------------------+
|      1 | Graphic Design     | ...
|      2 | Print Layout       | ...
|      3 | Web Design         | ...
|      4 | Web Development    | ...
|      5 | Video Production   | ...
|      6 | Animation          | ...                                    
|      7 | Mobile Development | ...
+--------+--------------------+--------------------------------------+
Enter fullscreen mode Exit fullscreen mode

We then insert data into majobskils as follows:

/*** INSERT DATA into majobskils ***/
INSERT INTO majobskils (job_id, cat_id)
VALUES ('1','3');
INSERT INTO majobskils (job_id, cat_id)
VALUES ('2','1');
INSERT INTO majobskils (job_id, cat_id)
VALUES ('3','1');
INSERT INTO majobskils (job_id, cat_id)
VALUES ('4','5');
/*** end INSERT DATA ***/
Enter fullscreen mode Exit fullscreen mode

The results:

mysql> SELECT * FROM majobskils;
+--------+--------+
| job_id | cat_id |
+--------+--------+
|      1 |      3 |
|      2 |      1 |
|      3 |      1 |
|      4 |      5 |
+--------+--------+
4 rows in set (0.01 sec
Enter fullscreen mode Exit fullscreen mode

We use job_id and cat_id because these ID numbers are unique to each row in majobo and maskils, respectively.

If we had instead used job_name and cat_name in majobskils, we would have redundant data - data that is duplicated elsewhere. Unlike name fields, ID fields are never subject to change, so using them to identify a row is safer and requires less storage and rework. The process of removing redundant data is called Database Normalization.

The structure for majobskils is shown in the following ERD as well as the relationships to majobo and maskils.

majobskills ERD

Joining majobo and maskils Tables

At this point, the database is set up to hold our job and the corresponding categories. Because the data is spread across three tables, we need to join the tables within our SELECT query. Joining tables means that we combine tables based on common fields (related fields). Joins do not physically join the tables, but rather join the query result sets from implied queries. There is no physical change to the database design.

/*** Get the category names associated with job_id #1 ***/

SELECT maskils.cat_name FROM majobo,maskils,majobskils
WHERE majobo.job_id = 1
AND majobo.job_id = majobskils.job_id
AND majobskils.cat_id = maskils.cat_id;
Enter fullscreen mode Exit fullscreen mode

We get:

+------------+
| cat_name   |
+------------+
| Web Design |
+------------+
1 row in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

If we look at the ERD above, we can follow the query through the linking fields. A table like majobskils is called a linking tables (or mapping tables) because it links together (maps) other tables like majobo and maskils.

Adding Technology Classifications Table: teknoma

We will handle adding technology classifications just as we did the categories. A variety of media technologies exist: XHTML, PHP, Flash, Photoshop, etc. Because we want our database to capture what technology was used for each work, we will create a technologies table: tbl_tech. Like previous tables, tbl_tech will have an ID column, tech_id, that is the primary key and is also set to auto increment. It will only hold information about technologies.

/*** CREATE teknoma table ***/
CREATE TABLE teknoma (
    tek_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tek_name VARCHAR(20) NOT NULL
);
/*** end CREATE ***/
Enter fullscreen mode Exit fullscreen mode

We load data from a local file with all the definitions of the technologies used:

mysql> LOAD DATA LOCAL INFILE 'teknoma.txt' INTO TABLE teknoma;
mysql> select * from teknoma;
+--------+--------------+
| tek_id | tek_name     |
+--------+--------------+
|      1 | Vector Art   |
|      2 | Pixel Art    |
|      3 | HTML5        |
|      4 | CSS3         |
|      5 | Javascript   |
|      6 | ReactJS      |
|      7 | PHP          |
|      8 | Laravel      |
|      9 | Ruby         |
|     10 | Rails        |
|     11 | Python       |
|     12 | Django       |
|     13 | REST API     |
|     14 | MySQL        |
|     15 | PostreSQL    |
|     16 | iOS          |
|     17 | Andriod      |
|     18 | React Native |
|     19 | Video        |
|     20 | AR / VR      |
+--------+--------------+
20 rows in set (0.00 sec)
Enter fullscreen mode Exit fullscreen mode

Like majobskils, we will create majobtek which will link majobo to teknoma. Both job_id and tek_id are foreign keys within majobtek.

/*** CREATE majobtek Table ***/
CREATE TABLE majobtek (
job_id INT NOT NULL,    
tek_id INT NOT NULL
)TYPE = myisam;
/*** end CREATE TABLE ***/
Enter fullscreen mode Exit fullscreen mode

And comparing the two tables and joining in the corresponding job to tek:

majobo 

+--------+-------------+-------------------------+-------------------+
| job_id | job_name    | job_desc            ...      |
+--------+-------------+-------------------------+-------------------+
|      1 | The Warehouse  | ... The web space was required  ... |
|      2 | Aim to Succeed | This was a graphic design poster ... |
|      3 | Oiling Life    | ... The brochure was to be ... |
|      4 | Dying to Give  | The main piece was a video documentary ...  |
+--------+-------------+-------------------------+-------------------+
Enter fullscreen mode Exit fullscreen mode

We get:

/*** INSERT DATA into majobtek ***/
INSERT INTO majobtek (job_id, tek_id)
VALUES ('1','3');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('1','4');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('1','7');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('1','12');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('2','1');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('2','2');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('3','1');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('3','2');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('4','18');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('4','19');
INSERT INTO majobtek (job_id, tek_id)
VALUES ('4','20');
/*** end INSERT DATA ***/
Enter fullscreen mode Exit fullscreen mode

So the fun part begins. To see what technology was used in what project, we create the following queries. If a, b, and c look confusing in the following query, don't be alarmed. a, b, and c are just alias table names. In essence, a, b, and c just represent a shorter way to type the table names that are used later in the query:

/*** Get the technology names used with job_id #1 ***/
SELECT c.tek_name FROM majobo a, majobtek b, teknoma c
WHERE a.job_id = 1
AND a.job_id = b.job_id
AND b.tek_id = c.tek_id;
Enter fullscreen mode Exit fullscreen mode

We get:

+----------+
| tek_name |
+----------+
| HTML5    |
| CSS3     |
| PHP      |
| Django   |
+----------+
4 rows in set (0.08 sec)
Enter fullscreen mode Exit fullscreen mode

Then:

/*** Get the job names used with tek_id #1 ***/
SELECT a.job_name FROM majobo a, majobtek b, teknoma c
WHERE c.tek_id = 1
AND a.job_id = b.job_id
AND b.tek_id = c.tek_id;
Enter fullscreen mode Exit fullscreen mode

We get:

+----------------+
| job_name       |
+----------------+
| Aim to Succeed |
| Oiling Life    |
+----------------+
`
So our majobtek ERD looks like so:

![majobtek ERD](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jnzwa60jn9i9bw4r86t0.png)

## Adding the Clients Table:  maklayo

Appropriately, maklayo will have klayo_id as the primary key set to auto increment.
Enter fullscreen mode Exit fullscreen mode


sql
/*** CREATE maklayo table ***/
CREATE TABLE maklayo (
klayo_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
klayo_name VARCHAR(20) NOT NULL
);

We then insert client names from a text file:
Enter fullscreen mode Exit fullscreen mode


sql
/*** LOAD DATA INTO maklayo ***/
LOAD DATA LOCAL INFILE 'maklayo.txt' INTO TABLE maklayo;
`
And again a table linking jobs to clients:

`/*** CREATE maklayojobo Table /
CREATE TABLE maklayojobo (
job_id INT NOT NULL,

klayo_id INT NOT NULL
)TYPE = myisam;
/ end CREATE TABLE ***/

We insert data:
Enter fullscreen mode Exit fullscreen mode


sql
/*** INSERT DATA into maklayojobo /
INSERT INTO maklayojobo (job_id, klayo_id)
VALUES ('1','1');
INSERT INTO maklayojobo (job_id, klayo_id)
VALUES ('2','4');
INSERT INTO maklayojobo (job_id, klayo_id)
VALUES ('3','5');
INSERT INTO maklayojobo (job_id, klayo_id)
VALUES ('4','6');
/ end INSERT DATA ***/

To make this a little interesting, here is a query that finds out which technologies were used on a particular job for a particular client:
Enter fullscreen mode Exit fullscreen mode


sql
/*** Get the technology names used for klayo_id #1 ***/
SELECT e.tek_name FROM maklayo a,
maklayojobo b, majobo c, majobtek d, teknoma e
WHERE a.klayo_id = 1
AND a.klayo_id = b.klayo_id
AND b.job_id = c.job_id
AND c.job_id = d.job_id
AND d.tek_id = e.tek_id;


We get:
Enter fullscreen mode Exit fullscreen mode


sql
+----------+
| tek_name |
+----------+
| HTML5 |
| CSS3 |
| PHP |
| Django |
+----------+
4 rows in set (0.01 sec)



Below is the ERD that makes the possible queries above happen:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ah1h20ae7hvd18acoruz.png)

This concludes our designing a database in MySQL using the CLI.

It is possible to do design and build a MySQL database with the a Graphic User Interface. That is in our next article, [**Designing a database using MySQL Workbench.**](https://dev.to/afrowave/designing-a-database-with-mysql-using-the-workbench-part-3-1nn8) 

_This article was first published on Medium.com_
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

jacksonsantin profile image

Login/cadastro com firebase + Vue JS #PASSO 1️⃣

Jackson Dhanyel Santin -

laustke profile image

I created a video tutorial on how to make a responsive sidebar using CSS and vanilla Javascript

Louis Austke -

intersystemsdev profile image

LLM Models and RAG Applications Step-by-Step - Part II - Creating the Context

InterSystems Developer -

paharihacker profile image

React Performance Optimization Techniques: Memoization, Lazy Loading, and More

Rajesh Dhiman -