MySQL open source modelling tools

There are many alternatives to design visually your MySQL databases, but if you require them to be free or open source, the chances are limited to only two: DBDesigner and MySQL Workbench.

Comparing DBDesigner and MySQL Workbench

MySQL workbench is a full featured tool from Oracle to manage MySQL databases. It has installers for Linux, Windows and Mac, and it comes with a GPL License.

DBDesigner is a desktop application focused on modelling databases. It can generate SQL for various RDBMS, including MySQL. It’s only available for Windows, but you can run it without problems in Linux with wine.

I’ve been working with both tools, but I have to say sincerely that, for modelling purposes I prefer DBDesigner. These are my motivations:

DBDesigner is lightweight and faster: MySQL Workbench has many more features regarding MySQL, aside of modelling, but it’s heavier to load.
DBDesigner is more usable:
- You can create relations with just two clicks. Relations will create the apropiate fields in the tables (i.e. foreign key fields), constraints, indexes, etc…
- When you add a foreign key to a table (i.e. User belongs to Group), the user group receives a new field after the primary key, group_id. Fields are ordered by default, first primary key, then foreigns, then the rest of the fields.
- When manually ordering fields in Mysql workbench, you can only add fields at the end of the table, and then move them one step at a time, to the position that you want. Imagine a table with 15 fields…. this is time consumming and a bit anoying (I should post a feature request, I know…).
- Creating and using your own custom types is easy in DBDesigner (for example you can define a type “PHONE” and asign it to a CHAR(15). You can do it in Mysql Workbench too, but when you add a new field you have to select the field type from a list with the mouse, no keyboard or auto-complete. Again, time consumming needlessly.
Forward engineering:
- With both tools you can transfer the changes from your ERD to a Mysql Schema. I have to say that with DBDesigner its a two-clicks process. With Mysql Workbench again there is an assistant that will execute the SQL script at the end.
Other tools of DBDesigner:
- Standard inserts: With DBDesigner you can define standard inserts for some tables (i.e. difficulties table: hard, medium, low…). It will ask you to syncronize them when doing forward engineering.
- Database documentor: DBDesigner can generate an HTML report of your design containing all the fields, comments etc..
- Maintenance web: DBDesigner can generate a simple PHP application that will let you manage the data in the database.

It’s clear I love DBDesigner.

DBDesigner Fork

DBDesigner is a discontinued project. There is also a fork of the project at SourceForge, but it seems it’s also dead. I have used both, and, by now, I have found no differences between them.

Connecting to the database

To connect to the MySQL database, I recommend you to create a dedicated user with all the privileges over the target schema. Then the only thing you have to know is that DBDesigner is a bit old, and it uses the mysql old passwords format. So after creating your user at the database, and assign all the privileges you will need to launch this command:

SET PASSWORD FOR 'your_user'@'your_host' = OLD_PASSWORD('your_password');

FLUSH PRIVILEGES

If you do it from phpMyAdmin, there is an option to alter the user password using the old format also.

Table engine specification in SQL

This tool is old, but you’d like to use the newest MySQL version. If you do it, you’ll realize that when you make a database synchronization, you get some syntax errors. This is due to the fact that the clause “TYPE InnoDB” to specify the engine of the table is deprecated since MySQL v.5. Now you have to use “ENGINE InnoDB“.

The code is at sourceforge, but I don’t know Kylix. On the other hand, the project seems to be frozen or dead, if you look at the age of the issues. So I managed to find a solution to this problem, ’cause I really like this tool.

What I did:

Download DBDesignerFork and uncompress it to a folder.
Edit directly the file DBDesignerFork.exe, the main executable of the application. I did directly with the vi editor (I’m on Linux).
Search for the string: “TYPE=” and change it to “–PE=”.
Save the file and you’re done.

What I’ve done is locating in the binary file where is the literal “TYPE=” that the executable is using to generate de DDL of the table. Then, putting a double dash, what I’ve done is to comment the rest of the DDL command. The TYPE clause is the last one, so it doesn’t matter if I comment the line. The table will be created using the default engine of the target database, InnoDB in my case.

You could say I’m a bit brute, but the fix took me one minute, and it’s working.

You can download directly the modified version from here.