View source for Using MySQL Workbench to Create a Database Model

<table border="0" cellspacing="0">
<tr>
<td width="20%">[[Using MySQL Workbench to Execute SQL Queries and Create SQL Scripts|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right"></td>
<tr>
<td width="20%">Using MySQL Workbench to Execute SQL Queries and Create SQL Scripts<td align="center"><td width="20%" align="right"></td>
</table>


<htmlet>mysql</htmlet>

Perhaps the most powerful feature of the MySQL Workbench tool is the ability to design and manage models. In this chapter we will work through the steps necessary to design a simple schema using MySQL Workbench and then generate a SQL script and feed it through to a database server to create the corresponding physical database. 

In addition to allowing new models to be designed, the workbench tool also includes reverse engineering capabilities, enabling a model to be generated from an existing database.

== Creating a New Model ==

To create a new model, start the MySQL Workbench tool and click on the ''Create New EER Model'' option located in the Data Modeling column of the home screen. A new panel will be added to the workbench labeled Model:
 

[[Image:mysql_workbench_model.jpg|Creating a database model in MySQL Workbench]]


Begin the modeling process by double clicking on the tab labeled mydb MySQL Schema. In the resulting properties panel change the name of the schema to mySample:
 

[[Image:mysql_workbench_schema_name.jpg|Changing schema name in MySQL Workbench]]


Close the schema property panel by clicking on the small “x” next in the Schema tab. Having given our schema a name, we can now add a table to the model. This is achieved by double clicking on the ''Add Table'' button in the Tables panel:
 

[[Image:mysql_workbench_add_table.jpg|Adding a table to a new model in MySQL Workbench]]


Change the table name to inventory and then select the Columns tab to begin the process of adding columns to the table:
 

[[Image:mysql_workbench_table_columns.jpg|Adding columns to database table]]


By default, the workbench will create a non-null primary key named idinventory. For the purposes of this exercise we will keep this column, though in practice this can be changed. Select the second line, double click in the Column Name field and name the column product. Accept the default value of VARCHAR for the datatype. Repeat these steps to add a price column of type FLOAT and a quantity column of type INTEGER. Each of these should be declared as non-null by checking the boxes in the NN column:
 

[[Image:mysql_workbench_table_columns_configured.jpg|Columns added to a MySQL Workbench database table model]]


Before proceeding, save the model using the ''File -> Save Model'' menu item, or using the appropriate toolbar button.

== Generating a Model Diagram ==

To generate an EER diagram of our model, select the ''Model -> Create Diagram from Catalog Objects'' menu option. The diagram illustrated in the following figure will then be generated:
 

[[Image:mysql_workbench_model_diagram.jpg|EER Model Diagram in MySQL Workbench]]


Though we aren’t creating any relationships in this example, it is important to note the vertical array of buttons to the left of the diagram that allow such relationships to be created within the diagram tool.

Return to the model by selecting the ''Model'' tab beneath the workbench toolbar.

== Generating a Script and Creating a Database ==

Having created a model, the next step is to generate a SQL script from the model and feed it through to a database server where it will be executed to create the physical database and table. To achieve this, select the ''Database -> Forward Engineer…'' menu option. In the resulting dialog, a range of selections are available to filter and modify the script that will ultimately be generated. Unless you have specific requirements, leave the default selections unchanged. In addition, the objects that are to be generated may also be selected. Since this is a very simple example, and all we have to generate is table information, select only the ''Export MySQL Table Objects'' option and click Next to review the generated SQL script:
 

[[Image:mysql_workbench_model_generated_script.jpg|A SQL script generated from a database model in MySQL Workbench]]


Assuming the script appears as expected given the initial model from which it has been generated, click Next to specify the server to which the script is to be sent (or save the script if it is to be used later, perhaps within the SQL Editor or mysql client):
 

[[Image:mysql_workbench_model_generated_to_database.jpg]]


Select the stored connection to which the script is to be sent together with any other connection parameters that are required, click on Execute and enter the password if required to do so. The success or otherwise of the execution will then be reported in the following screen. Assuming a successful operation, the new database will now be present on the designated database server.

== Reverse Engineering an Existing Database ==

In addition to aiding in the design of new models, a model may also be generated from an existing database (a concept referred to as reverse engineering). To reverse engineer an existing database, select the ''Create EER Model from Existing Database'' link from the Database Modeling column of the MySQL Workbench home screen and configure the connection to the desired database server in the resulting dialog. Having connected successfully and fetched the database information proceed to the next screen to select the schemata to be reverse engineered. A good example here if you do not have any existing databases is to reverse engineer the ''mysql'' database (the database used internally by MySQL). After the wizard has finished fetching additional information, select the Import MySQL table objects option on the Select objects to reverse engineer screen and click Execute.

Upon completion of execution, two new tabs labeled Model and EER diagram respectively will appear in the workbench. The model provides access to the schema for the database and tables:
 

[[Image:mysql_workbench_reverse_engineered_model.jpg|A MySQL Workbench reverse engineered database model]]


Similarly, the EER Diagram tab may be used to view the model of the reverse engineered model:


[[Image:mysql_workbench_reverse_engineered_diagram.jpg|A MySQL Workbench reverse engineered database model]] 


== Summary ==

In this chapter we have looked at creating a database model using the MySQL Workbench tool and also reverse engineering an existing database to extract the model. In terms of model design, the example outlined in this chapter was quite simple. With a little experimentation, however, it will quickly become clear that there is very little that can be done with SQL commands that cannot also be achieved using the workbench tool.

<htmlet>mysql</htmlet>

<hr>
<table border="0" cellspacing="0">
<tr>
<td width="20%">[[Using MySQL Workbench to Execute SQL Queries and Create SQL Scripts|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right"></td>
<tr>
<td width="20%">Using MySQL Workbench to Execute SQL Queries and Create SQL Scripts<td align="center"><td width="20%" align="right"></td>
</table>