Designing a database with MySQL using the Workbench — Part 3
Tags: mysql, database, webdev, programming
Published: 2023-10-28 20:15:27

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**](https://dev.to/afrowave/designing-a-database-with-mysql-using-the-command-line-interface-part-1-1ffe) and [**Part 2**](https://dev.to/afrowave/designing-a-database-with-mysql-using-the-command-line-interface-part-2-26of). 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’_](https://downloads.mysql.com/docs/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 <strong>EER(Entity-Entity Relationship)</strong>, export it into the existing live database and make queries from Workbench.

## Workbench

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

![Workbench splash screen](https://cdn-images-1.medium.com/max/800/1*7WyKGSpzEMHbPiy1hbrz1w.png)

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:

![Workbench Dashboard](https://cdn-images-1.medium.com/max/800/1*ZJPrvjmFyMjLPf02UtcZTA.png)

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

![Workbench Query Window](https://cdn-images-1.medium.com/max/800/1*nlgwXXeG8RWb-nUIm3vn-w.png)

### a) Adding the Designers’ Table: waseenoma

With _waks_mona_ selected, click on **Add Table**:

![Workbench Add Table](https://cdn-images-1.medium.com/max/800/1*X4-xemF2_60fsc4GewrKsw.jpeg)

Add the new name add the following columns:

![Workbench Table Add Columns](https://cdn-images-1.medium.com/max/800/1*06mgOz2os6EF-GVNgX_1Jw.png)

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:

![Workbench Review Script](https://cdn-images-1.medium.com/max/800/1*XFFODuZjr9d7WwvXPo4w6Q.png)

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

![Inspect Table](https://cdn-images-1.medium.com/max/600/1*OiZkyZW3lYZ2ZDkw-G7aEQ.png)

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

```sh
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 **:**

![Insert Data](https://cdn-images-1.medium.com/max/800/1*N9_sSm7hu6C1tZdkZxqaYw.png)

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

![Apply to save data](https://cdn-images-1.medium.com/max/800/1*-8nOwUDc0MrvIHenvZ_r7Q.png)

Click on **Apply:**

![Review script to apply data](https://cdn-images-1.medium.com/max/800/1*Kl9-JbTEzoSDMai1oYgfQg.png)

Click on the **Apply** again.

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

```sh
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**:**

![Workbench create EER Dashboard](https://cdn-images-1.medium.com/max/800/1*FaLn2b4ilFGHJJCgexBoiQ.png)

Click on the “Create EER Model from Database”.

![Workbench Set EER parameters](https://cdn-images-1.medium.com/max/800/1*zYMnS00B3TBaoKCSHpezeA.png)

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:

![Workbench Connect to database](https://cdn-images-1.medium.com/max/800/1*7Kwd9AteX8OsE4H07Viybw.png)

Click on _continue_:

![Workbench Connect to database 2](https://cdn-images-1.medium.com/max/800/1*IjFDCShjCVbxJaVg0mNmYg.png)

We choose our database _waks_noma_:

![Workbench Connect to database 3](https://cdn-images-1.medium.com/max/800/1*h__lLtRvUqXFNjpEkNqb5w.png)

We _execute_:

![Workbench Connect to database 4](https://cdn-images-1.medium.com/max/800/1*pX8slYocux9v87B7YuU1dg.png)

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

![Workbench View EER](https://cdn-images-1.medium.com/max/800/1*Mzm7fJqv6QLSuUNINr2-pw.png)

### c) Joining majobo and waseenoma: majobnoma Table

![Workbench join tables](https://cdn-images-1.medium.com/max/800/1*m_wDwsfKIa0XVmPWOhlSOg.png)

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

![Workbench join tables 2](https://cdn-images-1.medium.com/max/800/1*-t2GLMznWhWfBqP701wTVw.png)

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

![Workbench join tables 3](https://cdn-images-1.medium.com/max/800/1*CMgPZkPKc6Lc3dPzAFBq3Q.png)

Once we finish the connecting the joining tables; _majobskils_, _makalyojobo_ and _majobtek_, the new EER looks like so:

![Workbench View join tables](https://cdn-images-1.medium.com/max/800/1*0JNaY7IzRf5gsyhSYJzMgQ.png)

#### Exporting the EER to waks_noma

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

```sh
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.**

![Workbench Link EER with database](https://cdn-images-1.medium.com/max/800/1*SyEtO6eHwSNyc50TOJfRUg.png)

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](https://dev.mysql.com/doc/workbench/en/wb-forward-engineering-sql-scripts.html).

![Workbench Link EER with database 2](https://cdn-images-1.medium.com/max/800/1*_hRCNtJHZbPIdLuleRKqRA.png)

The “Export …” will be highlighted:

![Workbench Link EER with database 3](https://cdn-images-1.medium.com/max/800/1*NRKTw_Mlk6ghMnH4ejkDPQ.png)

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

![Workbench Link EER with database 4](https://cdn-images-1.medium.com/max/800/1*L484tZK5jruUK_mVAQRlAQ.png)

The following window pops up:

![Workbench Link EER run SQL script](https://cdn-images-1.medium.com/max/800/1*RiV_gGo-K9_WvFxn0jJWFA.png)

And if you have done everything right:

![Workbench Link EER success](https://cdn-images-1.medium.com/max/800/1*q_9sAtbWgj97nNKAzvUipA.png)

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

![Workbench check table majobnoma](https://cdn-images-1.medium.com/max/800/1*b_AYiFlGo4OYwdt2O0Frxw.png)

And checking through the CLI:

```sh
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.