Using Tools for Database Design

It’s a good idea to use a tool to draw your ER diagrams; this way, you can easily edit diagrams as you refine your designs, and the final diagram is clear and unambiguous. There are a large number of programs that can be used for this purpose. A good free tool that is available for both Linux and Windows is Dia; you can download the latest version from http://www.gnome.org/projects/dia. Mac OS X users can use the OmniGraffle program that comes bundled with the operating system. Windows users can also use Microsoft Visio.

A screenshot of Dia is shown in Figure 4-14.

Using the Dia program to draw an ER diagram

Figure 4-14. Using the Dia program to draw an ER diagram

When you open the program, you should first select the ER sheet of shapes from the drop-down list in the middle of the control window (where the ER label appears at the right of the figure) and then select from the entity and relation shapes.

You can assign properties to shapes by double-clicking on them. For example, you can mark an attribute as being a key or a weak key, and you can mark an entity’s participation in a relation as being total or partial.

The open source MySQL Workbench program is a very powerful visual database design tool available as part of the MySQL GUI Tools Bundle from the MySQL AB downloads page at http://dev.mysql.com/downloads.

Figure 4-15 shows a screenshot of using MySQL Workbench to design the flight database. You can select tables and relations from the toolbar icons on the left of the screen, and double-click on each object to set properties such as attributes and relationship cardinality.

A screenshot of the MySQL Workbench program to design the Flight database

Figure 4-15. A screenshot of the MySQL Workbench program to design the Flight database

A very useful feature of MySQL Workbench is that it can export your design as SQL statements ready to use on a MySQL database. Even better, it can connect to a MySQL database to export a design directly. You can also reverse-engineer an ER model from an existing database, edit the model, and then export the modified design back to the MySQL database. Note that this program is currently in the beta testing phase, so you should use it with care.

Get Learning MySQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.