In working with nonprofit organizations, I have become familiar with the need for powerful, low-cost tools. Recently, I was part of a team launching a WordPress-based website to provide both a public and a members-only presence. We knew that the entity-relationship (ER) model would be complex since it had to support WordPress, a membership extension, a mailing list extension, and organization-specific data. WordPress is designed to use the open source MySQL database, so a natural fit for ER modeling was the MySQL Workbench. In this article, I will describe the MySQL Workbench, which is a visual database design tool, and also some of our findings in using it at a nonprofit for a medium-sized project.
Workbench is part of the MySQL database product family that also includes the following:
- MySQL Enterprise
- MySQL Enterprise Monitor
- MySQL Cluster
- MySQL Embedded Database
- MySQL Connectors
Editions of these products are available for free download and other editions are sold commercially. MySQL also sells support for their products via MySQL Enterprise Production Support. In addition to the MySQL products, there are many partners who sell compatible products, support, and training. Overall, this is a compelling example of the power of open source to provide an inexpensive option while creating an ecosystem with plenty of opportunity for profit within the nonprofit community.
Version 5.1 of Workbench was released in June 2009, enabling database professionals to visually design, generate, and manage all types of databases on Windows, Linux, and MacOS platforms. Workbench includes everything needed to create and document complex ER models. It can reverse engineer the ER diagram by analyzing the structure of an existing database, and it can forward engineer the scripts and live tables from an ER diagram. It also has a “Diff” function that enables it to understand the differences between an ER model and the corresponding live database. This change management capability is critical in complex environments. With thousands of users, MySQL Workbench has been used for Web, OLTP, and data warehouse databases. MySQL products are especially compelling for Internet-based applications because open-source licensing enables users to spin up additional production or development instances as needed. This explains why cloud computing providers and open-source products such as Joomla and WordPress use MySQL as the foundation.
Workbench comes in two editions: the “Community” edition, which is available for free download, and the “Standard” addition, which is $99 per developer per year. The primary difference between the editions is that the Standard version includes additional features for validation and documentation as well as technical support. More information about Workbench and the MySQL family of products is available at www.MySQL.com.
Workbench is stable and mature for an open-source product. It has a graphical interface that is intuitive for those who are familiar with ER modeling, and, of course, it uses standard notation for ERDs. The interface to edit properties uses a pane at the bottom of the screen much like Adobe Dreamweaver, rather than the pop-up windows that are common in other products, and it is logical and easy to use. One would expect strong and simple integration when the design tool is made by the same company that makes the database platform, and Workbench does not disappoint. The ability to create multiple diagrams for a given schema is useful, and the ability of the Standard edition to export to multiple formats would be helpful for documentation and presentations, though we only used the Community edition.
Some beta versions of the product were unstable; however, that has improved greatly in the production version. The caveat would be to not use a beta version of Workbench for a production project, even though it is a development tool and “behind the scenes” from an end-user perspective. The instability of the beta could impact project timelines. Workbench requires a very powerful desktop and still runs somewhat slowly when compared to similar tools. Also, the validation capability of the Community edition is extremely limited and will let you make mistakes without warning.
MySQL Workbench is a case study in consolidation in the software industry. It is the successor to DBDesigner, an open-source tool created by fabFORCE.net. The team of people supporting DBDesigner was hired by MySQL in 2004, which means MySQL effectively acquired DBDesigner. MySQL was acquired by Sun in 2008, and Sun was acquired by Oracle in 2009. There is some question about the future of MySQL given the potential for competition with Oracle’s for-profit database tools. Fortunately, it is also a case study in the power of open source. Companies can be confident in their ability to continue to run and support their internally developed systems because they have the full source code of the underlying product. In the worst case scenario, should Oracle decide to kill MySQL, it is likely that another company would create a fork and continue to support MySQL under a new name. However, MySQL is still a viable option. Oracle had previously acquired the InnoDB backup and transactional storage tools for MySQL, so now the MySQL team is working together with the InnoDB team. While the future of MySQL could be brighter than ever, a company undertaking a larger project or a long-term commitment to a tool would have to consider Oracle’s recent acquisition of Sun as a risk.
While many ER modeling tools are powerful, flexible, and support a range of database platforms, MySQL Workbench is an excellent choice for organizations that primarily use the MySQL database. The Community edition has the features that you need for an excellent integration with the database platform, but even the Standard edition is inexpensive. In the case of the nonprofit networking organization that I am assisting, Workbench enabled us to document the ER diagram for WordPress and the extensions so that we could see what we needed to add for our custom purposes. Our site is fundamentally database-driven, so having a good ER diagram was the foundation of our effort and having the right tool made a difficult project much easier.