MacLochlainns Weblog

Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:

You enter user defined types by entering a name value and choosing valid MySQL type value before clicking the Add button. When you’ve added your last user defined type, click the OK button instead of the Add button. The next screen shot shows how you can create Oracle Database 11g native data types, specifically the NUMBER and VARCHAR2 data types.

Hopefully, this has shown that you can create User Defined Types let you use MySQL Workbench to create Oracle ERD models. Here’s an example of a table with Oracle’s NUMBER and VARCHAR2 data types:

Yes, MySQL Workbench is a marvelous tool with wide potential for use to solve problems with MySQL and other databases.

One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.

It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.

Hope this helps those working with MySQL Workbench.

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The system_user table serves as the Access Control List (ACL) for the application.

Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.