Open Source Applications For Database Modelling

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.

Join the conversation

Toad Data Modeler

There is also Toad Data Modeler (before known as CaseStudio 2). The freeware version doesn't include all features of Toad Data Modeler and allows you to save a model with less than 25 entities. (Which is enough for simple projects...). Only for MS Windows.

Best solution for mysql but

Best solution for mysql but not free is Navicat.
http://www.navicat.com/product.html/

Thanks For the tips. We're a

Thanks For the tips. We're a school looking to migrate from Linux, but need to find tools for our students which can replace the windows applications we use today.

Power*Architect is not so powerful...

It does not support custom types :(

Still looking for good open source database modeling tool... ERwin is a dream...

This was the first time i

This was the first time i hear about power architect, and i was curious actually, because i have some problems with DBDesigner, so i downloaded it. This is incredible.

Really thank from the bottom of the bottom of my heart :)

i really like this post

i really like this post having good information about drupal 6 version ....

Keep your comments relevant, written in good English and don't spam. Let's create useful and valuable discussions. Markdown is welcome.

Add your comment