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 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 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
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.
To open the table editor, either change back to the
MySQL Model tab and right-click on the
movies table, or right-click on
movies in the EER diagram and select an
Edit 'movies' option.
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 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 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 9, Database Design / Modeling.