Visual Database Design with MySQL Workbench

January 18, 2021

MySQL Workbench is a graphical visual database design tool with multiple functionalities. This tutorial will cover database design and modeling with MySQL Workbench.

Prerequisites

This article is suitable for beginner to intermediate MySQL Workbench users. It requires no prior knowledge of MySQL Workbench.

However, knowledge in relational databases and database design may be required. To get started with the MySQL database, check the MySQL Tutorial website.

Introduction to MySQL Workbench

There are two editions of MySQL Workbench: the community edition and the commercial edition. The community edition is open source. Both editions are available for three major platforms; MS Windows, macOS, and Linux. The commercial edition comes with more functionalities at a cost.

Some of the extra functionalities available in the commercial edition are:

• MySQL Enterprise backup
• MySQL Firewall
• MySQL Audit

MySQL Workbench has five main functions:

Database design (data modeling)

This involves creating simple to complex entity-relationship (ER) models. Reverse engineering creates a database from ER models. Forward engineering creates an ER model from a live database.

Developing SQL

MySQL Workbench has a built-in SQL editor with syntax highlighting and auto-complete. It is used to interact with the MySQL Server.

Administration

Some of the MySQL Workbench database administrative functions are:

• Backup
• Recovery
• Audit
• Monitoring server performance
• Checking database health
• User management

MySQL Workbench has a visual performance dashboard. The visual performance dashboard enables database administrators to view key performance indicators. Below is a screenshot of the MySQL Workbench visual performance dashboard.

Image source

Data migration

MySQL Workbench is used to migrate databases from other relational database management systems (RDBMS). Some of the supported RDBMS are PostgreSQL, MS SQL Server, SQLite, MS Access, Sybase, and Sybase SQL Anywhere.

MySQL enterprise support

MySQL Workbench enterprise edition supports MySQL enterprise products.

In this tutorial, the focus will be on database design.

Setting up MySQL Workbench

Workbench is one of the MySQL products. Use MySQL Installer to manage MySQL products installations. Find the list of MySQL Workbench supported platforms on MySQL official website. For the MySQL Workbench hardware requirements, check the image below.

Image source

Installing MySQL Workbench on Windows

Download MySQL Installer from the official website. Install MySQL Workbench using the downloaded installer. For this installation, I recommend the default configurations, as shown in the screenshot below.

The Developer Default setup type comes with MySQL Workbench and other developer tools.

You can find more detailed installation guidelines for MySQL Workbench on MySQL Workbench Manual.

Verifying MySQL Workbench installation

Launch MySQL Workbench.

You can do this from the installation wizard.

• Linux: Launch by typing the command mysql-workbench. Alternatively, navigate to Activities > MySQL WorkBench.
• macOS: Navigate to Applications > MySQL Workbench.
• Windows: Navigate to Start > Programs > MySQL > MySQL Workbench.

Make sure there is a connection to the MySQL Server local instance, as shown in the screenshot below. If there is no connection, click the + icon to create a new connection, as highlighted in the screenshot below.

Provide the connection details.

The created connection will be displayed, as shown in the screenshot below.

Database design with MySQL Workbench

This section will create a new ER model and then translate it into a physical MySQL database. On MySQL Workbench, navigate to File > New Model as shown below.

Save the model. Double click MySQL Schema and change the name from mydb to booksdb. Click the Add Diagram icon to create a new EER diagram. Refer to the screenshot below.

After adding a new diagram, a new window will be opened, as in the screenshot below.

Scenario

In this tutorial, we will model and create a database that will be used to keep book details. The database should store books with author and publisher details. We will skip the database normalization process details.

The final database will have three main entities with attributes, as shown