In this post I will present the process for creating the MySQL database used in the VoiceHotel project, using the MySQL Workbench Community Edition, focusing on the features offered by this tool. The VoiceHotel project was the Grand Prize Winner of the Sun Students Contest in 2008, MySQL and GlassFish step.

What is MySQL Workbench?

mysqlWorkbenchSplashIn general terms we can say that MySQL Workbench is a tool that enables a DBA or developer in general, to create and manage visually all types of MySQL databases, from a simple database in a web application, to a even more complex, such as those in Data Warehouses or OLTP systems.

Tool highlights

  • Visual Database Design. Accelerates the development, reducing the chance of errors in common tasks. Provides schema validation for the model, help on the use of best practices for data modeling, avoiding errors in the construction of ER diagrams or creating the database physically.
  • Forward and Reverse Engineering. Enables the creation of a complete database model from a SQL script or an existing live database, including the ER diagram with pre-arranged objects in the same. Also offers the construction of the database physically by visual modeling.
  • Change Management. MySQL Workbench includes schemas synchronization and comparison utilities. For example, a DBA can compare two live databases or compare one modeling made in MySQL Workbench and an live database, viewing the differences, with the possibility of synchronization between a model and database or vice versa.
  • Database Documentation. Includes DBDoc that enables a DBA or developer to deliver point-and-click database documentation. Models can be documented in either HTML or plain text format, and includes all the objects and models in a current MySQL Workbench session.

MySQL Workbench is available in two versions:

  • MySQL Workbench Community Edition — In the open source GPL license.
  • MySQL Workbench Standard Edition — Available through an annual contract. Includes some additional modules and plugins. Click here for more details.

Modeling the database

Due to low complexity presented by the database used in the application, I will present here the three possible ways to create the database using the MySQL Workbench Community Edition. Below a picture with the completed ER diagram. The complete script for creating database can be found here.
Modelo ER Finalizado

Reverse engineering SQL scriptreverseengineer1

Certainly the quickest way to create the database, but obviously you will need to have beforehand the file with the SQL statements for creating the structure of the MySQL database.

Create a new data model (file > new), and then start importing SQL statements accessing the file menu > import > reverse engineer MySQL create script.

reverseengineer2 In new window, select the file with the instructions to be used to model creation. When you click to continue the tool will perform some validation in SQL script and then starts the creation of the model structure. Finally, in the last step of this wizard is offered the option to automatically create a ER diagram with the tables already arranged.

-

reverseengineer3 With the newly created data model, you can now work freely, adding new tables, views, documenting existing structures, editing the model structure, as well as the ER diagram.

ER Diagram

The data model creation through the ER diagram is a great choice for cases where is necessary to model creation since the beginning, where we do not have a structure prior to import, to start a model with some pre-existing structure.
ER Diagram - New To add a new ER diagram access the menu Model > Add diagram. Or if you prefer use the shortcut CTRL+T. For a detailed view of the options available in the ER diagram editor click on the image at your right.

Insofar as that new objects are included in the diagram, you can edit all its related properties just double clicking the object that you want, to open the object properties editor.

When the inspected object is a table, in addition to various options for editing columns, primary keys, foreign keys, tiggers, etc, we have a data editor, where you can generate insert statements just filling the values for the columns.
erdiagram3
editordata Still talking about tables, relationships between entities in the existing model, can be made fully visually, using the relationships options, located on the toolbar that is available on the left side of the interface for editing the ER diagrams.

Schematic Editing (Advanced)

schemata1

The schematic editing allows the developer quick access to information and properties of the object (tables, views, routines and groups of routines), but without the visual support of the ER diagram. It is very useful when we have a structure already established in the database model and we need to maintain in some of the existing objects in a fast, easy and intuitive way, with the support of tools to validate the changes thus preventing possible inconsistencies.

Publishing the Database Model

After the creation of the data model, you can export the entire contents of the model to a script containing all the necessary SQL commands to create the database physically. In the Standard version of MySQL Workbench, it is possible to create the physical database, without leaving the environment.

For the Community Edition we can use the following command line, making use of tools available in MySQL installation:

mysql -u user -p < script.sql

where user is the database user and -p will ask you for the password.

Conclusion

In this article, I presented the basic features of MySQL Workbench and there are clearly many other concepts that can be explored, as the creation of stored procedures (Functions, Triggers and Procedures), creation of views and indexes.

MySQL Workbench is a great option for all MySQL users, from developers to more experienced users, like DBAs, etc. Counting with the option of commercial license, this is more obvious because there is a considerable increase in the number of modules and plugins, offering several features which are disabled in the Community Edition version. However, the Community Edition version is ahead of many commercial and non-commercial tools available, offering all the resources needed for modeling MySQL databases.

About these ads

Join the conversation! 1 Comment

  1. […] leave a comment » update 1: english readers can view the translated version of this post here. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Category

mysql, mysql workbench, tools

Tags

, ,