Planning a database with MySQL Workbench

Table Map

A small database is easy to plan on paper, but the structure quickly becomes more complex as you add more elements. MySQL Workbench can help you keep the tables arranged.

By Falko Benthin

Ping Han, 123RF

A vast number of software applications require some form of database. The more complex the project, the more complicated, time-consuming, convoluted, and error-prone the structures of the matching databases become. Software manufacturers are aware of this problem, which explains the plethora of tools for visual database planning and generation.

The choice of visualization tools is fairly restricted in Linux. Developers can choose between commercial and free offerings; the free tools are typically offered by the database manufacturers themselves. Although commercial tools typically support multiple databases, tools by database vendors are often intended to support only their own product. MySQL Workbench [1], which is designed to work with the MySQL database system, is one of these vendor-specific database support tools. MySQL Workbench is a GUI-based tool for planning and editing MySQL schema.

The MySQL database management system isn't just for full-time database developers. MySQL databases are popular with millions of web designers and everyday sysadmins. Even if you're one of those occasional MySQL developers, you might find a tool like MySQL Workbench helps you work more efficiently.

MySQL Workbench is a GPLv2-licensed tool that is based on experience with, and feedback relating to, the DBDesigner 4 [2] data modeling tool. The Workbench is available in community and standard versions; the standard version differs from the community edition in that it costs 79 euros per year, is capable of checking database schemas and database models, and includes the database documentation.

The community version of MySQL Workbench includes a large collection of features. Database administrators and developers can use it to plan tables, views, indices, stored procedures, and triggers; parse schemas from existing databases to visualize them ("reverse engineering"); synchronize schemas with existing databases ("change management"); and export and print model diagrams. MySQL Workbench is intended as a database design tool; it does not support querying or modifying records. (Sun offers the MySQL Query Browser for this.) Here, I used the stable 5.1 version of MySQL Workbench.

First Launch

After installing MySQL Workbench (see the "Installation" box), launch the tool by typing mysql-workbench at the command line (Figure 1). The overview lets you create the new database model visually via EER diagrams (Extended Entity-Relation Diagrams) or enter the schema. Because the visual method is easier for most users, I'll focus on that.

Figure 1: MySQL Workbench launches to a tidy workspace.

Thanks to the intuitive controls, the learning curve is fairly flat. The workspace has clearly outlined areas: On the left is a toolbox with the most frequently used steps; on the right is the navigator (which is useful with larger databases), the catalog (which you can use to access tables, views, and procedures), and an information box. At the bottom of the application window are the object editors for objects you create or open.

Suppose I wanted to create a simple contact database to track the objects I have lent out to friends. I'll start by creating the People table, which includes the first and family names, and an ("Attribute") column with integer values for the primary key. To create the table, press the icon on the right of the window, or just press T. After positioning the table, you can use the tabs in the table editor to define its name, the column names and types, any foreign keys, the triggers, and the partitions.

Each of these items will take you to more dialogs, where you can define data types, conditions, primary keys, or options if you are using foreign keys. The software lists the attributes below the table name in the workspace. For each attribute, there is a colored icon that lets you easily identify the attribute type. A key icon points to a primary key, for example. In our lab, it took quite a while for the icons to change when I edited the columns; if in doubt, it is always a good idea to check in the table editor.


Binary packages of MySQL Workbench are available for Ubuntu and Fedora [3]. If you are lucky, you might find the software in repositories belonging to other distributions or third parties. For example, Norbert Tretkowski has created a Workbench package [4] for Debian.

If you urgently need specific modifications, you can download the source code for the tool, unpack the zipped tarball, change to the new directory this step creates, and take a look at the README file before you do anything else. The file lists the software's dependencies, among other things. You can then build the software by issuing the commands ./ --prefix=path and make -j3 install in a terminal window.

You will need to replace path with the name of the directory where you will be installing the software. The build took quite a while in our lab, but it completed successfully because I had resolved all the dependencies.

Foreign Keys and Layers

After creating the first table, you can create more tables and define foreign keys, which show the relationships between the fields in various tables. To create them, either use the toolbox or the table editor. Because the program was pretty quirky in our lab when I tried to create foreign keys with the toolbox, I would recommend using the editor. Now change to the Foreign Keys tab and create a foreign key that points to the first table. To do this, you just need to click the corresponding box: MySQL Workbench will suggest a name for the foreign key and display a selection list of existing database tables. The software displays the candidates in the area next to the foreign key name and referenced tables. In doing so, it will only suggest fields with plausible data types.

Workbench typically only creates relations that match data types. For example, a People record could point to multiple address records and many phone numbers - referred to as a one-to-many relation. If the relation type is not right, right-click the relation and make adjustments in the relation editor. If a table contains multiple foreign keys, the software highlights them in different colors when you mouse over them.

To keep track of related areas in large-scale databases, MySQL Workbench introduces the concept of layers. A layer lets you color-highlight multiple tables to group them visually. To use layers, either use the toolbar, or press L and drag the mouse over all the objects you want to add to the layer (Figure 2).

Figure 2: Layers help you keep track of large-scale projects.


You can use EER diagrams to create views in a similar way to creating tables, but stored procedures and functions need to be defined in the physical schema, not in a diagram. MySQL Workbench also refers to stored procedures and functions as routines. This example uses a small procedure that counts the number of objects currently lent out.

To do this, I'll move from the EER diagram to the MySQL model. When you get there, click Add Routine below Routines. The routine editor launches at the bottom of the window, and you can then write your procedure (Figure 3). The EER diagram only displays groups of routines. Create a group, then drag and drop the required routines into the Routine Group Editor.

Figure 3: The Routine editor appears at the bottom of the window.

Reverse Engineering

If you already have a ready-to-use database model, you can either upload it directly to your database server, or use a file. To do so, select File | Export | Forward Engineer Create SQL Script (Shift+Ctrl+G), enter the filename (without the filename the application will display the script, but will not store it), and, if necessary, select the required options. In a second step, the software will ask you which objects you would like to export (Figure 4) before finally creating the script.

Figure 4: A few clicks let you specify what MySQL Workbench should include in the script.

To transfer the model directly to a database server, you need to enter the database server connection parameters in Database | Manage Connections. To send the newly defined schema straight to the server, select Forward Engineering below Database. You can just click your way through this to send the database to the desired location.

The process for using existing database models ("Reverse Engineering") is just as simple. You can either run an SQL script to import the model, or you can grab it from an active database server. A wizard exists in the Databases menu to assist with this process. The program prompts you for the connection data and the schema you want to grab.

If the schema contains more than 15 tables, the view can become cluttered: the 145 tables I imported from a hospital information system overlapped so badly that I needed to initiate a major cleanup (Figure 5).

Figure 5: Importing a large number of tables can make the EER diagram view cluttered.

Thanks to forward and reverse engineering, you can use MySQL Workbench to modify existing schemas and synchronize the resulting databases (Database | Synchronize Model, or File | Export | Synchronize with SQL Create Script).


MySQL Workbench offers a number of features that are really useful when planning larger scale databases. However, the software is fairly resource-hungry and fairly slow at times on our non-state-of-art lab machine (Pentium 4, 2.5GHz, 1GB RAM). Unfortunately, the program crashed several times, so a function that lets users save their work automatically at regular intervals would be a good idea.

MySQL Workbench is not really suitable for planning databases in any other database management system. If you work with another DBMS, you should consider a different tools, such as Database Visual Architect [5], Sybase PowerDesigner [6], or the slightly ancient DBDesigner 4 [2]. Having said this, if you are looking for a free tool to help you design MySQL databases, MySQL Workbench is a good choice. It has a good feature set that will make the lives of database developers and administrators much easier. The upcoming MySQL Workbench 5.2 supports server queries and administrative tasks. To provide these functions, the developers built some parts of MySQL Query Browser and MySQL Administrator into the application.

[1] MySQL Workbench:
[2] DBDesigner 4:
[3] MySQL-Workbench downloads:
[4] MySQL Workbench for Debian "Lenny":
[5] Database Visual Architect:
[6] Sybase PowerDesigner: