MySQL Workbench. Database creation

MySQL Workbench – A web developer grows with the projects he creates and develops. As the projects grow, the complexity of the software part increases, the amount of data processed by it inevitably increases, as well as the complexity of the data schema. Interaction with other web developers shows that MySQL databases are very popular among us, and to manage them – the well-known PHPMyAdmin.

Moving from small projects to large, from cms to frameworks, many, like me, remain true to MySQL. However, to design a complex database with a large number of tables and links, the capabilities of PHPMyAdmin are catastrophically lacking. So I decided to write an overview of MySQL Workbench – a great free desktop program for working with MySQL.

MySQL Workbench is a database design tool

MySQL Workbench is a database design tool that integrates database design, modeling, creation and operation into a single seamless environment for the MySQL database system.

I must say, the program is really great. It allows you to quickly and gladly encapsulate the schematics of the project data, design the entities and connections between them, painlessly implement changes to the scheme and just as quickly and painlessly synchronize it with a remote server. And the graphic editor of EER-diagrams, reminiscent of funny cockroaches, allows you to see the overall picture of the data model and enjoy its lightness and elegance. After the first try, this tool becomes an indispensable assistant in the combat arsenal of web programmer.

Download MySQL Workbench

The MySQL Workbench distribution is available on this page. The latest version of the program at the time of writing is Version 6.1. Before downloading, you need to select one of the following platforms:

• Microsoft Windows (MSI Installer and ZIP archive are available).
• Ubuntu Linux
• Fedora
• Red Hat Enterprise Linux / Oracle Linux
• Mac OS X

Once you have selected a platform, you are invited to register or log in to Oracle. If you do not want, there is a link “No thanks, just start my download” below – click on it.

Getting started with MySQL Workbench

The start screen of the program reflects the main directions of its functionality – design of database models and their administration:

In the upper part of the screen there is a list of connections to MySQL servers of your projects, and a list of the latest open data models – at the bottom of the screen. The work usually starts with creating a data schema or loading an existing structure into MySQL Workbench. Let’s get to work!

Creating and editing a data model

To add a model, press the plus sign next to the “Models” heading or select “File → New Model” (Ctrl + N):

On this screen, enter a name for the database, select the default encoding and fill in the comment box if necessary. You can start creating tables.

Adding and editing a table

The list of project databases and the list of tables within the database will be located in the “Physical Schemas” tab. To create a table, double-click on “+Add Table”:

This opens a user-friendly interface for editing the list of fields and their properties. Here we can set the field name, data type, as well as set different attributes for the fields: assign the field as Primary Key (PK), mark it as Not Null (NN), BIN, Unique (UQ) and others, set the field to auto-increment (AI) and default (Default).

Index management

You can add, delete and edit table indexes in the “Indexes” tab of the table management interface:

Enter the name of the index, select its type, then check the list of fields participating in this index in the required order. The order of the fields will correspond to the order in which the checkboxes were placed. In this example, I added a unique index to the username field.

Links between tables

The installation of external keys and linking of tables is only possible for InnoDB tables (this storage system is selected by default). To manage the bindings, each table has a “Foreign Keys” tab:

To add a connection, open the “Foreign Keys” tab of the child table, enter the name of the external key and select the parent table. Then in the middle part of the tab in the column Column, select the key field from the child table, and in the column Referenced Column – the corresponding field from the parent table (the type of fields must match). When creating external keys in the child table, the corresponding indexes are automatically created.

In the “Foreign Key Options” section, we configure the behavior of the external key when changing the corresponding field (ON UPDATE) and deleting (ON DELETE) the parent record:

• RESTRICT – generate an error when changing/deleting a parent record
• CASCADE – update the external key when changing the parent record, delete the child record when deleting the parent
• SET NULL – set the value of the external key NULL when changing / removing the parent (unacceptable for fields that have NOT NULL flag set!).
• NO ACTION – do nothing, but in fact the effect is similar to RESTRICT.

In the example above, I added an external key to the UserProfile child table to link to the User parent table. When editing the userId field and removing items from the User table, similar changes will automatically happen to the associated entries in the UserProfile table.

Filling the table with basic data

When creating a project, you often need to add start data to the database. These can be root categories, user admins, etc. In the MySQL Workbench table management there is an “Inserts” tab for this purpose:

As you can see from the example, if you need to apply some MySQL function to data before writing it to the database, this is done using the \func functionName(‘data’) syntax, for example, \func md5(‘password’).

After entering data, you should save it to the local database by clicking the “Apply Changes” button.

Creating an EER diagram (entity-communication diagram)

There is an EER-diagram editor for representing the data schema, entities and their relationships graphically in MySQL Workbench. To create a diagram, double-click the “+Add Diagram” icon at the top of the database management screen:

In its interface you can create and edit tables, add different types of connections between them. To add a table that already exists in a chart to a chart, simply drag and drop it from the Catalog Tree panel.

To export the schematic data to a graphic file, select “File → Export” and then one of the options (PNG, SVG, PDF, PostScript File).

Importing an existing data schema (from SQL dump)

If we already have a data schema, you can easily import it into MySQL Workbench for further work. To import a model from an SQL file select “File → Import → Reverse Engineer MySQL Create Script…”, then select the desired SQL file and click “Execute >”.

MySQL Workbench also provides for importing and synchronizing data models directly with a remote server. To do this, you need to create a remote access connection to MySQL.

MySQL Workbench Tutorial. Introduction To MySQL Workbench. MySQL DBA Training. Edureka (video)