This section provides a tutorial introduction to MySQL models by showing you how to create a new database model, and how to forward engineer a model to a live MySQL server.
Alternatively, you can create a model from a database by using the reverse engineering wizard. For additional information, see Section 18.104.22.168, “Reverse Engineering a Live Database”.
Start MySQL Workbench. On the home screen, click the [+] icon next to the Models section on the bottom of the page, or select , . A model can contain multiple schemata. Note that when you create a new model, it contains the
mydbschema by default. You can change the name of this schema to serve your own purposes, or delete it.
Click the Physical Schemata toolbar to add a new schema. The default schema name is "new_schema1", now change it to “dvd_collection” by modifying its Name field. Confirm this change in the Physical Schemata panel. Now you are ready to add a table.button on the right side of the
Double-click Add Table in the Physical Schemata section.
This automatically loads the table editor with the default table name table1. Edit its Table Name field and change the table name from “table1” to “movies”.
Next, add columns to your table. Double-click a Column Name cell, and the first field defaults to “moviesid” because (by default) MySQL Workbench appends “id” to the table name for the initial field. Change “moviesid” to “movie_id” and keep the Datatype as
INT, and also select the PK (PRIMARY KEY), NN (NOT NULL), and AI (AUTO_INCREMENT) check boxes.
Add two additional columns using the same method as described above:
Column Name Data Type Column Properties movie_title VARCHAR(45) NN release_date DATE (YYYY-MM-DD) None
For a visual representation (EER diagram) of this schema, select, to create the EER Diagram for the model.
In the table editor, change the name of the column “movie_title” to “title”. Note that the EER Diagram is automatically updated to reflect this change.Note
To open the table editor, either change back to the MySQL Model tab and right-click on the
moviestable, or right-click on
moviesin the EER diagram and select an option.
Save the model by choosing “Home_Media” and then the model., from the main menu, or click on the toolbar. Enter a model name at the file prompt. For this tutorial, enter
Before synchronizing your new model with the live MySQL server, confirm that you already created a MySQL connection. This tutorial assumes you followed the previous Section 5.2, “Creating A New MySQL Connection (Tutorial)” tutorial to create a MySQL connection named MyFirstConnection, although an alternative connection can also work.
Now forward engineer your model to the live MySQL server. Select Forward Engineer to Database wizard., from the main menu to open the
The Connection Options page selects the MySQL connection and optionally sets additional options for the selected MySQL connection. We do not require connection changes so click .Note
You may decided to choose a different MySQL connection here, but this tutorial uses MyFirstConnection.
The Options page lists optional advanced options. For this tutorial, you can ignore these and click Next.
Select an object to export to the live MySQL server. In this case, we only have one table (dvd_collection), so select
dvd_collectionand click .
The Review SQL Script page displays the SQL script that will be executed on the live server to create your schema. Review the script to make sure that you understand the operations that will be carried out.
Clickto execute the Forward Engineering process.
The Commit Progress page confirms that each step was executed. Click to view the logs. If no errors are present, click to close the wizard.
dvd_collectiondatabase is now present on the MySQL server. Confirm this by opening the MySQL connection and viewing the schema list, or by executing
SHOW DATABASESfrom the MySQL Command Line Client (mysql).
Ensure that your model is saved. Click Save Model to Current File on the main toolbar.
For additional information about data modeling, see Chapter 9, Database Design / Modeling.