4.2 Creating a Model

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.

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

    Figure 4.13 Getting Started Tutorial - Home Window

    Getting Started Tutorial - Home Window

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

    Figure 4.14 Getting Started Tutorial - New Schema

    Getting Started Tutorial - New Schema

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

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

  5. 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 Datatype as INT, and also select the PK (PRIMARY KEY), NN (NOT NULL), and AI (AUTO_INCREMENT) check boxes.

  6. Add two additional columns using the same method as described above:

    Column NameData TypeColumn Properties
    movie_titleVARCHAR(45)NN
    release_dateDATE (YYYY-MM-DD)None

    Figure 4.15 Getting Started Tutorial - Editing table columns

    Getting Started Tutorial - Editing table columns

  7. For a visual representation (EER diagram) of this schema, select Model, Create Diagram from Catalog Objects to create the EER Diagram for the model.

    Figure 4.16 Getting Started Tutorial - EER Diagram

    Getting Started Tutorial - EER Diagram

  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

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

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

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

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

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

    Note

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

  13. The Options page lists optional advanced options. For this tutorial, you can ignore these and click Next.

    Figure 4.17 Getting Started Tutorial - Options

    Getting Started Tutorial - Options

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

    Figure 4.18 Getting Started Tutorial - Select Objects

    Getting Started Tutorial - Select Objects

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

    Figure 4.19 Getting Started Tutorial - Review SQL Script

    Getting Started Tutorial - Review SQL Script

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

  17. 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).

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