This tutorial describes 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 models icon in the sidebar and then click the + icon next to Models. Alternatively, you can click and then from the menu (shown in the figure that follows).
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 default schema as needed or you can delete it.
Click the Physical Schemas toolbar to add a new schema. The default schema name isbutton on the right side of the
new_schema1, which you can now change to
dvd_collectionby modifying its Name field. Confirm this change in the Physical Schemas panel shown in the next figure. Now you are ready to add a table.
Double-click Add Table in the Physical Schemas section.
This automatically loads the table editor with the default table name
table1. Edit the Table Name field to change the table name from
Next, add columns to your table. Double-click a Column Name cell and the first field defaults to
moviesidbecause (by default) MySQL Workbench appends
idto the table name for the initial field. Change
movie_idand keep the Datatype as
INT, and also select the PK (PRIMARY KEY), NN (NOT NULL), and AI (AUTO_INCREMENT) check boxes.
Add the two additional columns described in the following table. The figure that appears after the table shows all three columns in the
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 EER Diagram, which displays diagram representation of the movies table and columns.and then to create the EER Diagram for the model. The next figure shows the a new tab titled
In the table editor, change the name of the column
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 choosingand then from the menu, or click the icon on the menu toolbar. For this tutorial, type
Home_Mediaand then click .
Before synchronizing your new model with the live MySQL server, confirm that you already created a MySQL connection. This tutorial assumes you have created a connection already. If not, see Section 5.2, “Creating A New MySQL Connection (Tutorial)” and use that 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 as follows:
Selectand then from the menu to open the Forward Engineer to Database wizard.
The Connection Options step selects the MySQL connection and optionally sets additional options for the selected MySQL connection. Make any necessary connection changes and then click.Note
You may decided to choose a different MySQL connection here, but this tutorial uses MyFirstConnection.
The Options step lists optional advanced options (as shown in the figure that follows). For this tutorial, you can ignore these options and click Next.
Select an object to export to the live MySQL server. In this case, there is only one table (
dvd_collection.movie). Select the
Export MySQL Table Objectscheck box (as the figure that shows) and then click .
The Review SQL Script step 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 step confirms that each task was executed. Clickto 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).
Click the Save Model to Current File icon on the menu toolbar to save the model.
For additional information about data modeling, see Chapter 9, Database Design and Modeling.