Are there any open source applications to model my database? Many developers have asked me that question quite often lately.

When coding Drupal modules having a good picture of the database model can help a lot to write the right queries, well, actually this applies to any programming project that uses a database with more than a few tables.

Even if there are many commercial applications, many of them quite stable and complete, I think their prices are way too much for most developers, specially for open source developers like most of us Drupal dudes. Want numbers? I got numbers: CA Erwin Data Modeler costs US$ 3995. With that much dough I'd prefer to complete my list of gadgets I want for 2007 and add another laptop with Linux to my arsenal.

So, what are the options to model your database with an open source tool?

Some years ago I found DBDesigner 4 and used it in a few projects to model on MySQL, the only RDBMS it handles. I was quite happy to know that MySQL had taken over the product to relaunch it as MySQL Workbench, however, MySQL has focused on other tools and Workbench progress has been slow. I've recently read in their forum that they were almost finished with other tasks and would return to work with MySQL Workbench. Good to know.

DBDesigner 4 is still available and is a good choice if all you need is MySQL support. The installation is a little difficult in Ubuntu, although I didn't have any trouble with Fedora..

And what happens if, like me, you also need to model PostgreSQL databases? The only application I found for that some months ago was Mogwai ER-Designer, which works with MySQL, PostgreSQL, Oracle and a few other commercial RDBMS. Mogwai ER-Designer is written in Java, so it runs in most operating systems with a JVM.

Unfortunately Mogwai's interface still needs some work and creating models is not too user friendly, even when they correctly use domain dictionaries. I also think development progress is going a little slow.

What I'm using now, and recommend, is Power*Architect, which, like Mogwai, is a Java application and works with PostgreSQL, Mysql, Oracle and others.

I think PowerArchitect has a better interface than Mogwai, creating models and generating SQL is quite easy and takes just a few clicks. PowerArchitect has been declared open source just a few months ago, way to go guys!, and there are still many features to add and improve (like support for native auto increment fields in both MySQL and PostgreSQL) but it seems the development team is right on track and version 1.0 is coming.

So there you have it, there's still hope and we'll be seeing more mature database modelling tools in the open source arena soon.

Oh, and if you know of any other let us know.

Update September 19: Sean just told me that native types auto_increment in MySQL and serial in PostgreSQL are supported in the latest versions of Power*Architect.
<!--break-->