Using MySQL Workbench to design the VoiceHotel Database Model
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.
In 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.
- 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.
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.
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.
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.
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.
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)
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.
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.