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.
Start MySQL Workbench. On the Home window, click the
[+] icon next to the
Models section on the bottom of the page,
or select File, New
Model. A model can contain multiple schemata.
Note that when you create a new model, it contains the
mydb schema by default. You can change the
name of this schema to serve your own purposes, or delete it.
Click the + button on the right side of 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.
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 several columns. 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
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|
For a visual representation (EER diagram) of this schema, select Model, Create Diagram from Catalog Objects 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.
There are several ways to open the table editor. Either
change back to the MySQL Model tab and
right-click on the
movies table, or
movies in the EER diagram
and select an Edit 'movies'
Save the model by choosing File, Save Model from the main menu, or click Save Model to Current File on the toolbar. Enter a model name at the file prompt. For this tutorial, enter “Home_Media” and then Save the model.
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 4.1, “Creating a MySQL Connection” 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 Database, Forward Engineer... from the main menu to open the Forward Engineer to Database wizard.
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 Next.
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_collection and 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.
Click Next to execute the Forward Engineering process.
The Commit Progress page confirms that each step was executed. Click Show Logs to view the logs. If no errors are present, click Close to close the wizard.
dvd_collection database is now
present on the MySQL server. Confirm this by opening the MySQL
connection and viewing the schema list, or by executing
SHOW DATABASES from the MySQL Command Line
Ensure that your model is saved. Click Save Model to Current File on the main toolbar.
For additional information about data modeling, see Chapter 7, Data Modeling.