MySQL Workbench

9.3.1 Creating a Model

This tutorial describes how to create a new database model and how to forward-engineer a model to a live MySQL server.

Note

Alternatively, you can create a model from a database by using the reverse engineering wizard. For additional information, see Section 9.4.2.2, “Reverse Engineering a Live Database”.

  1. Start MySQL Workbench. On the home screen, click the models view from the sidebar and then click (+) next to Models. Alternatively, you can click File and then New Model from the menu (shown in the figure that follows).

    Figure 9.22 Getting Started Tutorial - Home Screen

    Content is described in the surrounding text.

    A model can contain multiple schemas. Note that when you create a new model, it contains the mydb schema by default. You can change the name of this default schema as needed or you can delete it.

  2. Click the + button on the right side of the Physical Schemas toolbar to add a new schema. The default schema name is new_schema1, which you can now change to dvd_collection by 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.

    Figure 9.23 Getting Started Tutorial - New Schema

    Content is described in the surrounding text.

  3. Double-click Add Table in the Physical Schemas section.

  4. This automatically loads the table editor with the default table name table1. Edit the Table Name field to change the table name from table1 to movies.

  5. 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.

  6. Add the two additional columns described in the following table. The figure that appears after the table shows all three columns in the movies table.

    Column Name Data Type Column Properties
    movie_title VARCHAR(45) NN
    release_date DATE (YYYY-MM-DD) None

    Figure 9.24 Getting Started Tutorial - Editing table columns

    Content is described in the surrounding text.

  7. For a visual representation (EER diagram) of this schema, select Model and then Create Diagram from Catalog Objects to create the EER Diagram for the model. The next figure shows the a new tab titled EER Diagram, which displays diagram representation of the movies table and columns.

    Figure 9.25 Getting Started Tutorial - EER Diagram

    Content is described in the surrounding text.

  8. 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 movies table, or right-click on movies in the EER diagram and select an Edit 'movies' option.

  9. Save the model by choosing File and then Save Model from the menu, or click the Save Model to Current File icon on the menu toolbar. For this tutorial, type Home_Media and then click Save.

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:

  1. Select Database and then Forward Engineer from the menu to open the Forward Engineer to Database wizard.

  2. 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 Next.

    Note

    You may decided to choose a different MySQL connection here, but this tutorial uses MyFirstConnection.

  3. 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.

    Figure 9.26 Getting Started Tutorial - Options

    Content is described in the surrounding text.

  4. 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 Objects check box (as the figure that shows) and then click Next.

    Figure 9.27 Getting Started Tutorial - Select Objects

    Content is described in the surrounding text.

  5. 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.

    Click Next to execute the forward-engineering process.

    Figure 9.28 Getting Started Tutorial - Review SQL Script

    Content is described in the surrounding text.

  6. The Commit Progress step confirms that each task was executed. Click Show Logs to view the logs. If no errors are present, click Close to close the wizard.

  7. The new 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 Client (mysql).

  8. 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.

MySQL Documentation Library