MySQL Workbench allows you to create your tables, fields and relationships in a visual, simple and efficient way.
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.
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.
MySQL Workbench requires a connection to a database, so it must first be created with PHPMyAdmin.
Open PHPMyAdmin in your browser
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
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:
Test your connection, then close the settings window.
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.
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).
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.
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:
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:
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:
In the main menu, go to database => synchronize model.
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.
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.
From the "Model and data difference" screen:
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.
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.
Build up your database