MySql Workbench is a great tool not only for managing a Database but for Modeling Schemas. With the modeler you can very easily generate all the code required for creating Tables, Indexes and Table Associations.
A Schema is basically a collection of Tables, Views, Indexes, Users allowed to access, Triggers, Stored Procedures, etc. It is common to confuse a Database with a Schema and more when you only have one schema in your Database.
Prerequisites:
- Have downloaded and installed MySql workbench
- Have a MySql / MariaDB server installed and running.For local development you may refer to: Configure MariaDB in a Vagrant Virtual Machine
- Have a connection available in Workbech to the Db Server running. For local development you may refer to: Connect MySql Workbench to a running instance in a Vagrant Machine
Step 1. Create a new Schema model.
Click on the model tab then the plus sign:
And a new schema page is opened:
Step 2. Right click on mydb tab and select edit.
Change the name of the schema and also choose character set. Recommended is utf8mb4 and general_ci for collation:
Step 3. Double click on the Add Diagram icon at the top.
A new model diagram page is opened:
Step 4. Click on the table icon then click on the page.
Once the new table is placed, double click on it to edit its properties:
Give a name to this new table and then click on the Columns Tab. Then start adding all the columns you need. For each column you may want to choose data type, if nullable, etc.
Important: don't forget to save your model. just Click on File > Save Model as...
Step 5. If the next table is similar to the first one.. just copy - paste it to duplicate it. Then double click on it and change the name and update columns.
If you need to define table associations, just click on the type you want and then click on one table and then on the other:
Step 6. Once you have placed all Tables, Indexes, Associations, etc. then click on the Database menu option and select Forward Engineer.
A dialog opens up showing the details of the connection:
click on Next choosing the required options for each step. most of the time accept the defaults. once you reach the review Sql Script, you may want to save it for sharing or later use:
Click on next and Workbench will take care of applying this sql script to the new created schema. Close the dialog window and close the diagram tab. You will see that the tables are already in your schema:
The visual modeler is a great tool and it allows you to get a better picture of how the data is persisted.