How to build up your database with MySQL Workbench

MySQL Workbench Tutorial

The video shows step-by-step how to build your database schema & relations with the MySQL Workbench software.

This is an easy way to create your internal/external relations with foreign keys.

Once your database structure & relations are ready, the next step will be to open the CRUD Generator and begin to generate your Admin Panel content.

About MySQL Worbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.

https://www.mysql.com/products/workbench/

MySQL Workbench is free software provided by the MySQL team. It enables you, after setting up your connection, to create your tables, fields and relationships, and integrate them into diagrams.

The diagrams provide a functional visual of the model, which you can edit right away using a range of tools (creating/editing relationships, adding fields, etc.).

You then export to your database to synchronise it.

In short, MySQL Workbench makes working with your databases much more pleasant, simple and efficient than using the PHPMyAdmin interface.

Let's take a closer look at how to get started.

Create your database with phpMyAdmin

MySQL Workbench requires a connection to a database, so it must first be created with PHPMyAdmin.

1. Open PHPMyAdmin in your browser

2. Click the New button on the top left, enter a name for your new database, choose the charset in the dropdown menu (usually "utf8_general_ci" is a good choice) then click create.

   

Connect MySQL Workbench to your server

Now that you have created your database, the second step is to connect MySQL Workbench to it.

Open MySQL Workbench, then go to database => manage connections.
Create a new connection and enter your connection settings:

• hostname ("localhost" for a connection on your local server)
• port (usually "3306")
• password
• default schema ; you can choose your database here, or leave it blank for a global connection.

Test your connection, then close the settings window.

Create a new Model and schema

Click file => new model in the main menu. It creates a new project with a database named mydb.
Double-click the mydb tab to open it and enter your real database name.

Create your tables & fields

Close the database tab to go back to the main screen.

From here you can create your tables, add and parameterise your fields by selecting the appropriate options (Data type, primary key, not null, auto-increment, etc).

Add a new Diagram

Now we will create a diagram, which will then allow us to create the relationships between the tables in a few clicks.

Close your table tab then double-click the Add Diagram from the main screen.
This opens the diagram window.

The Catalog tree on the left shows your database schema with its tables.
Drag and drop the tables you want to add to the diagram into the main area.

Note that MySQL Workbench allows you to create several different diagrams in the same project, which is recommended if you have many tables. This will allow you to group the different logical parts of your project, and have a more readable view of each.
You can also create a global diagram that includes all your tables, and several sub-diagrams.

Create your relations (add foreign keys)

Now that your diagram is in place you can create the relationships between your tables.
The foreign key fields will be created and named automatically.

Several types of relationships are available from the toolbar:

• 1:1 non-identifying
• 1:n non-identifying
• 1:1 identifying
• 1:n identifying

Pour des explications sur les différents types de relations, voir le tutoriel The different kinds of relationships in a MySQL database

To add a relationship between two tables:

• Select the appropriate relationship type from the toolbar
• In the diagram, click on the header of the table you want to add the relational field to
• Then click on the header of the table you want to link
• Your relationship is generated. You can see the arrow that links the two tables in the diagram. When you hover the mouse over your relationship it is highlighted, along with the relevant fields: primary key of the origin table, relational fields of the destination table.

Export your database from Mysql Workbench to your server

So far we have been working with MySQL Workbench, but nothing has been transferred to the MySQL server.
MySQL Workbench allows you to synchronise your databases in both directions, easily and securely. Here is how to export from the software to your MySQL server:

1. In the main menu, go to database => synchronize model.

2. In the opened window, check your connection settings, and enter the name of your database in the "default schema" field, then click next.

   In the next step you are given options to skip certain synchronisations. You can ignore them and go to the next step.

3. Click the next button until you reach the "Model and data difference" screen (screenshot below).
   MySQL Workbench will connect to your database, retrieve and analyse its data.

   

4. From the "Model and data difference" screen:

   • The Model is the MySQL Workbench database schema
   • The Source is the server database schema

   From the "update" column, you can double-click the arrows to choose the direction of synchronisation for each table.
   Then click the next button.
   It'll show a preview of the SQL statements that will be sent to your server.

5. Click the Execute button to finalize the synchronisation, and MySQL Workbench will do the magic.

   

In addition to allowing you to structure your databases in a simple and efficient way, MySQL Workbench of course offers many options and possibilities.
You can for instance export diagrams in different formats (image, pdf), which allows you to share them with your co-workers or customers.
Furthermore, after saving your project in .wmb format you can reload it and start synchronising it on another server, which allows you to recreate and even share your database with a few clicks.

Combined with Flyspeed SQL Query, these two free programs are, to my mind, the perfect couple for working with MySQL databases.

PHP CRUD tutorial main page