DEV Community: Jimmy Gitonga

Designing a database with MySQL using the Workbench — Part 3

Jimmy Gitonga — Sat, 28 Oct 2023 20:15:27 +0000

If you have just stepped in, please have a look at the first two parts in this series, Designing a MySQL database using the Command Line Interface, Part 1 and Part 2. We continue to work with the database we developed in these two articles. As in the previous articles, pick any name for your tables in the database if you find these a bit too abstract.

Now that we are comfortable with the building of a database in MySQL through the Command Line Interface, let us look at designing the databases through the MySQL Workbench 6.3.x. For information on installation procedures for different OSs, look at the ‘workbench-en.pdf’ manual available from the MySQL website. It also has a tutorial on how to build a database in the Workbench.

In this part, we will work with the database “Waks_Noma” that we built previously. We will import the database into the MySQL Workbench(Workbench), understand the visual representation of our _waks_noma_database, add a table into the EER(Entity-Entity Relationship), export it into the existing live database and make queries from Workbench.

Workbench

When one opens Workbench, the initial screen looks like so:

To work on the waks_noma database, we will open our MySQL instance on our local machine.

Click on the Local instance 3306 button and you may be asked enter the name and password.

NOTE: When working in a multi-user environment and online to a live remote server, the name and password security issues should be taken very seriously.

We get:

The schemas of the databases present on the local computer appear Schemastab at the bottom left. We choose our waks_noma and collapse the Management tab.

a) Adding the Designers’ Table: waseenoma

With waks_mona selected, click on Add Table:

Add the new name add the following columns:

NOTE: Set the PK (Primary Key) to AI( auto-increment), and make sure all tthe colums are NN (NOT_NULL). We will click on Apply and we see something familiar:

Click on Apply to run the script, and get our new table, waseenoma:

Let us go to our command line and confirm that it works:

mysql> show tables;
+---------------------+
| Tables_in_waks_noma |
+---------------------+
| majobo              |
| majobskils          |
| majobtek            |
| maklayo             |
| maklayojobo         |
| maskils             |
| teknoma             |
| waseenoma           |
+---------------------+

Success.

b) Adding Designers’ names into ‘waseenoma’

Let us go back to our Schemas tab, select waseenoma table. Click on the icon on the right :

We insert our data in the editing tab that is named waseenoma 1 and click on the Apply changes to data :

Click on Apply:

Click on the Apply again.

Now to check that the data has really been applied to the waseenoma, at the command line we get:

mysql> select * from waseenoma;
+----------+--------------+-------------------+
| mnoma_id | mnoma_name   | mnoma_desc        |
+----------+--------------+-------------------+
|        1 | Mchora       | Graphics Chief    |
|        2 | Blossom      | Layout Sweetener  |
|        3 | Helium Ninja | Content Developer |
|        4 | Msafara      | Video Converger   |
+----------+--------------+-------------------+
4 rows in set (0.00 sec)

We are certain that the Workbench works and we can go onto work with EER diagrams.

In the Home tab and click on the “ > ” icon*:*

Click on the “Create EER Model from Database”.

Unless there is a good reason, like you have many users on the machine, leave these settings as they are. Continue to connect to the DBMS:

Click on continue:

We choose our database waks_noma:

We execute:

Once we have completed the steps, we open the EER Diagram and arrange our ERDs:

c) Joining majobo and waseenoma: majobnoma Table

Above the waseenoma table, go an pick “Place a New Table” and insert a table majobnoma. Edit majobnoma and in the Foreign Keys tab, create the FKs to majobo.job_id and wanoma.mnoma_id.

We notice the “Crow Feet” connection that automatically appear between the majobo table and the wanoma table. You will notice that the existing “joining tables” that we made directly in the command line don’t automatically relate to their respective table FKs.

We will correct this by changing them from the columns that are in the tables and using the Foreign Keys tab update them. Make sure that you connect the column in majobnoma and the referenced column both majobo and waseenoma.

Once we finish the connecting the joining tables; majobskils, makalyojobo and majobtek, the new EER looks like so:

Exporting the EER to waks_noma

Finally we need to implement this EER onto the actual database. When we query the database, we get:

mysql> show tables;
+---------------------+
| Tables_in_waks_noma |
+---------------------+
| majobo              |
| majobskils          |
| majobtek            |
| maklayo             |
| maklayojobo         |
| maskils             |
| teknoma             |
| waseenoma           |
+---------------------+
8 rows in set (0.07 sec)

As we can see the database does not know about the majobnoma table. In the Workbench, go to the Home tab and get the to the local instance. When you look at the tables, it is clear the majobnoma table is missing.

So we will set up Workbench to create the SQL statements we need to sync the EER diagram with the live database schema. Go to the File > Export > Forward Engineer SQL CREATE Script.

Add a name for the SQL script and press continue. The instructions for the options in this tab can be found here — Forward Engineering Using an SQL Script.

The “Export …” will be highlighted:

We export the table objects and save. Make sure you are on the local instancetab. We then open the script to run it:

The following window pops up:

And if you have done everything right:

Opening the Workbench local instance, we can see the majobnoma is in the database now:

And checking through the CLI:

mysql> show tables;
+---------------------+
| Tables_in_waks_noma |
+---------------------+
| majobnoma           |
| majobo              |
| majobskils          |
| majobtek            |
| maklayo             |
| maklayojobo         |
| maskils             |
| teknoma             |
| waseenoma           |
+---------------------+
9 rows in set (0.00 sec)

If you have reached this far, be rest assured you are on your way to being a on your way to being a MySQL ninja!

That concludes our series on designing relational databases using MySQL using the CLI and the Workbench desktop application. If you have enjoyed the series, please give us some feedback to encourage us to extend development of databases with MySQL or PostgreSQL.

Thanks for reading and we hope you at least enjoyed yourself.

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

Jimmy Gitonga — Sun, 06 Mar 2022 20:53:37 +0000

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)

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

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 ***/

We get:

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

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

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)

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:

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 ***/

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 ...  |
+--------+-------------+-------------------------+-------------------+

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 | ...
+--------+--------------------+--------------------------------------+

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 ***/

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

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.

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;

We get:

+------------+
| cat_name   |
+------------+
| Web Design |
+------------+
1 row in set (0.00 sec)

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 ***/

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)

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 ***/

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 ...  |
+--------+-------------+-------------------------+-------------------+

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 ***/

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;

We get:

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

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;

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.

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:

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:

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:

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:

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_

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

Jimmy Gitonga — Tue, 18 May 2021 19:46:18 +0000

I dived in to Python and Django became my go-to tool for the kind of projects I do (data-intensive). I decided at that time (late ’00s) that it would be nice to get to understand how I can work with the database, just in case my framework ORM needs help. So I came up with my personal learning exercises to become familiar with my database of choice then. I think that these series of articles will help the next lot of database ninjas.

MySQL

This article is adequate for an absolute beginner to begin to tinker with the MySQL database. If you do not have an installation on your localhost, get an installer here — Download MySQL Community Server.

Introduction

MySQL has an interesting story. Even with the purchase of MySQL by Oracle and the split that made MariaDB, MySQL is still the most popular Open Source Relational Database Management System (RDBMS) for Open Source software development.

MySQL 5 was an important release as it brought enterprise features such as Stored Procedures and Triggers into MySQL. Now with Big Data flying around all over the place, Relational Databases have simply had to be faster and more efficient. For ingesting lots of data to be sorted out later, NoSQL has been the answer. For data that is already structured, SQL is still the best way to go.

Today, unless one is building a “static” web site, a web developer will deal with databases at one point in time. A lot of frameworks and tools like SQLAlchemy abstract the database from the “middleware’, in this case, Python. For Frontend developers, once one is past HTML5/CSS3/ECMAScript 6 or the React /Vue “concepts” and enters into Rails(Ruby)/ Django(Python)Angular(JavaScript) web frameworks, a massive paradigm shift will occur. But in many cases, you may never write a line of SQL.

The Relational Database

A Database is a store for data, either as files or as URLs to where the actual data is are stored. In its basic form, a database can be a list of articles associated to each other by one or more attributes. For example, a list of men’s names and another list of dates.

A Relational Database (RDB) is where the list of men’s names can be related to the list of dates. Let the relation between the two lists be birthdays. The two lists would be put into a table as two columns and the table would called ‘birthdays’. This is table is a Relational Database. How one chooses to view the data from it; such as, how many men were born in April or when was John born, depends on the queries made.

MySQL is often the choice of web developers with database functionality needs. Other SQL databases such as PostgreSQL or Microsoft’s MSSQL Server may use slightly different query syntax, but the relational database design concepts shown will still apply.

This article assumes that MySQL 5+ is installed on the computer or server being used and the syntax used will work on your command line interface of choice.

Installation

I am on macOS. Once I followed all the instructions, I opened the Terminal to start up mysql. After a fresh install, the mysql command is buried in /usr/local/mysql/bin. Create an alias so that you can call the command easily. You can operate as root, though it is best practice to create a new user and login as the new user. I will assume that you know how and have logged in as a registered user and not as root. To get to know who to do that, check out the manual.

Previously, KEYWORDS were written in CAPITAL CASE letters. But now, keywords may be entered in any lettercase. So these are all the equivalent:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

Part 1: Designing an RDB using the Command Line Interface

I have picked specific names for my database and tables, and I encourage this practice as it improves security, how? It is harder for hackers to guess the names of the tables. Of course there must also be proper documentation and version control, oh yes, of the database.

I will use lower case letters to be in sync with CLI (Command Line Interface) commands. Let us see what we have for databases:

mysql> show databases;
+ — — — — — — — — — — +
| Database            |
+ — — — — — — — — — — +
| information_schema  | 
| mysql               |
| performance_schema  |
| sys                 |
+ — — — — — — — — — — +
4 rows in set (0.00 sec)

These are the default databases used by mysql. In the console printouts for the show databases command, I will leave out these particular databases.

The Database: Waks Noma

Our database will hold the information of members of a web design company, the technologies they use, the projects they have singly or jointly worked on and the duration of the projects.

Pick any name for your database since the ones I have used might be abstract and make sense in a pidgin of Swahili known as Sheng’.

Let the design company be called Waks Noma. So we create a database for the company:

mysql> create database waks_noma;
Query OK, 1 row affected (0.00 sec)

To see that we successfully created the table:

mysql> show databases;
+ — — — — — — — — — — +
| Database            |
+ — — — — — — — — — — +
| waks_noma           |
+ — — — — — — — — — — +

i) Creating the Jobs Table: majobo

We need to work out what we will be setting up as the item of the database that every thing else will revolve around. This item is what we give a “primary id”. We are choosing between the designer or the job element. This is thinking through and in many cases drawing out is Database Schema Design. Schema design should be taken seriously at the onset of every database development.

It is good practice, to plan out the relationships between items in the database. In our case, we want to show off the jobs, and then the designers on these jobs. So we will give jobs the Primary Key.

Our first table consist of the jobs done by the company, Waks Noma.

mysql> use waks_noma;

And then we create the table ‘majobo’:

create table majobo (
 job_id int not null auto_increment primary key,
 job_name varchar(20) not null, job_desc text not null,
 job_pic_url varchar(255) not null
);

We query the database to see the results,

mysql> show tables;

and we get:

+ — — — — — — — — — — -+
| Tables_in_waks_noma  |
+ — — — — — — — — — — -+
| majobo               |
+ — — — — — — — — — — -+
1 row in set (0.00 sec)

We check out whether the table is as we want it:

mysql> describe majobo;
We get:

+ — — — — — — + — — - — — —- + — — -+ — — — — - - -+ — — — — — — — +
| Field       | Type         | Null | Key | Default | Extra        |
+ — — — — — - + — — — — — -— + — - -+ — - + — — - -+ — — — — — — — +
| job_id      | int(11)      | NO   | PRI | NULL   | auto_increment|
| job_name    | varchar(20)  | NO   |     | NULL   |               |
| job_desc    | text         | NO   |     | NULL   |               |
| job_pic_url | varchar(255) | NO   |     | NULL   |               |
+ — — — — — — -+ — — — — — — + — — — + — — -+ — — -+ — — — — — — — +
4 rows in set (0.02 sec)

We are in business!

NOTE: One can store images in a MySQL database, but it is not advisable for speed and stability reasons. Instead store the URLs to the images and other media as shown.

ii) Loading the table with Values

There are two ways to load the database with values:

through the shell prompt
through uploading data from a text file

This is not only restricted to inserting data but deleting data too through the shell prompt. For data loading through a file, one deletes all the previous entries and makes corrections in the file, and uploads the data again. If the data set entered through the file is large and one need to make a change on one record then that too can be done.

a) Insert and Delete through the Shell Prompt

In the real world, the shell prompt is rarely directly used unless the user is adding the odd entry or two. However as MySQL script files are written and run, these instructions would reside in a script file. We are styling the code as if it were in a script file.

/*** INSERT sample data into majobo ***/

insert into majobo (job_name, job_desc, job_pic_url)
  values ('The Warehouse', 'Developing a comprehensive website that
  will deliver on all fronts. Client-side was developed in ReactJS.
  Server-side was developed Django REST API and incorporating some
  custom modules', 'https://www.waksnoma.ke/thewarehouse/'
);

/*** end INSERT ***/

The result looks like this, formatted to fit into the page:

mysql> select * from majobo;
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
| job_id  | job_name       | job_desc         | job_pic_url       |
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
| 1       | The Warehouse  | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules        | https://www.waksnoma.ke/thewarehouse/    |
+ — — — — + — — — — — — — -+ — — — — — — — — -+ — — — — — — — — — +
1 row in set (0.00 sec)

You will notice that we never put a value for the job_id. This shows that our auto_increment is working as it should. We add another set of data:

/*** INSERT sample data into majobo ***/

insert into majobo (job_name, job_desc, job_pic_url)
  values ('aim to succeed','This was a graphic design poster job
  that was to introduce the school open day. The client company has
  a group of schools',
  'https://www.waksnoma.ke/waks/aimingposter.jpg'
);

/*** end INSERT ***/

This gives us a table that has the following features:

mysql> select * from majobo;
+ - - - -+ - - - - - - - -+ - - - - - - - + - - - - - - - - - - - -+
| job_id | job_name       | job_desc      | job_pic_url            |
+ - - - -+ - - - - - - - -+ - - - - - - - + - - - - - - - - - - - -+
|      1 | The Warehouse  | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules       | https://www.waksnoma.ke/thewarehouse/  |
|      2 | aim to succeed | This was a graphic design poster job that was to introduce the school open day. the client company has a group of schools   | https://www.waksnoma.ke/waks/aimingposter.jpg |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
2 rows in set (0.00 sec)

Question: What if we want to put in content data that,
especially in the description, has an apostrophe, ' ?

Let's take the above example to illustrate this.

/*** INSERT sample data into majobo ***/

insert into majobo (job_name, job_desc, job_pic_url)
 values ("Aim to Succeed","This was a graphic design poster job that
 was to introduce the school's open day. The client company has a
 group of schools","https://www.waksnoma.ke/waks/aimingposter.jpg"
);

/*** end INSERT ***/

Note however instead of using the single quotation marks to isolate the data into strings, double quotation marks are used instead. This way the description will allow the “… school’s open day …” to enter into the database without issues. So our database now has:

mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id  | job_name     | job_desc      | job_pic_url             |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
|       1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules      | https://www.waksnoma.ke/thewarehouse/        |
|       2 | aim to succeed | This was a graphic design poster job that was to introduce the school open day. the client company has a group of schools | https://www.waksnoma.ke/waks/aimingposter.jpg   |
|       3 | Aim to Succeed | This was a graphic design poster job that was to introduce the school's open day. The client company has a group of schools | https://www.waksnoma.ke/waks/aimingposter.jpg |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
3 rows in set (0.00 sec)

NOTE: Double quotation marks work just as well as single quotation marks in defining strings in the data. This is true in a number of programming and scripting languages.

Sadly, in the majobo table, there are two entries that look similar and they need to be removed. We will remove entry id=2 and id=3.

mysql> delete from majobo where job_name = ‘Aim to Succeed’ order by job_id limit 2;

Result:

mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | job_name      | job_desc      | job_pic_url             |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
|      1 | The Warehouse | Developing a comprehensive website that will deliver on all fronts. Client-side was developed in ReactJS. Server-side was developed Django REST API and incorporating some custom modules | https://www.waksnoma.ke/thewarehouse/ |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +

To clear the entire table, the command is:

mysql> delete from majobo;
Checking the table:

mysql> select * from majobo;
Empty set (0.00 sec)

Resetting the AUTO_INCREMENT

Since we have erased all the data from the table, we need to reset our auto_increment in order for us to have data integrity.

mysql> alter table majobo auto_increment = 0;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0

b) Insert data from a File

The data to be put into majobo can be loaded from a file in either a remote location or from the local drive.

Here is a view of our text file with in plain text with tab separated values.


    The Warehouse   The client is an art and entertainment facilitator. The web space was required to showcase the various groups that they are involved with. It is a multimedia site with blogs and comments.     https://www.waksnoma.ke/waks/thewarehouse/
    Aim to Succeed  This was a graphic design poster job that was to introduce the school open day. The client company has a group of schools and the poster was to be put up in selected shopping centres and malls.   https://www.waksnoma.ke/waks/aimingposter.jpg
    Oiling Life The client is an established petroleum company that needed a company brochure of all their lubricating products. The brochure was to be given to the company's service stations and car speciality shops.   https://www.waksnoma.ke/waks/oilinglife.pdf
    Dying to Give   The main piece was a video documentary on the state of organ donation in East Africa and what needs to be done to make sure that the awareness is raised of this selfless giving. The client is a major cardiological hospital. https://www.waksnoma.ke/waks/dyingtogive.mov

Depending on where the file is on your local drive, our instructions to load it are:

mysql> load data local infile ‘[absolute_path]/mawaks.txt’ into table majobo;
Query OK, 4 rows affected, 4 warnings (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

Our table now looks like this:

mysql> select * from majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id | job_name      | job_desc      | job_pic_url             |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
|      1 | The Warehouse  | The client is an art and entertainment facilitator. The web space was required to showcase the various groups that they are involved with. It is a multimedia site with blogs and comments.  | https://www.waksnoma.ke/waks/thewarehouse/  |
|      2 | Aim to Succeed | This was a graphic design poster job that was to introduce the school open day. The client company has a group of schools and the poster was to be put up in selected shopping centers and malls. |
https://www.waksnoma.ke/waks/aimingposter.jpg |
|      3 | Oiling Life    | The client is an established petroleum company that needed a company brochure of all their lubricating products. The brochure was to be given to the company's service stations and car speciality shops.  |
https://www.waksnoma.ke/waks/oilinglife.pdf   |
|      4 | Dying to Give  | The main piece was a video documentary on the state of organ donation in East Africa and what needs to be done to make sure that the awareness is raised of this selfless giving. The client is a major cardiological hospital. | https://www.waksnoma.ke/waks/dyingtogive.mov  |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
4 rows in set (0.00 sec)

Going back to our assignment, let us lay out our table in a way we can see its design.

mysql> describe majobo;
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| Field       | Type         | Null | Key | Default | Extra        |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
| job_id      | int(11)      | NO   | PRI | NULL  | auto_increment |
| job_name    | varchar(20)  | NO   |     | NULL    |              |
| job_desc    | text         | NO   |     | NULL    |              |
| job_pic_url | varchar(255) | NO   |     | NULL    |              |
+ - - - - + - - - - - - -+ - - - - - - - + - - - - - - - - - - - - +
4 rows in set (0.24 sec)

Let us draw a graphical representation of majobo, we get:

The diagram shown above is an Entity Relationship Diagram. Database ERDs are used to show relationships between tables in database as well as table structures. Because ERDs help visualize table structures and relationships, they often lead us to better database designs.

Conclusion

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.

I will publish the next part - Designing a database with MySQL using a Command Line Interface — Part 2 in a few days.

This article was first published on Medium.com