Thursday, February 11, 2016

Free Entity-Relationship Database Modeling tools - Two viable choices

I've used Computer Associates (CA) ERwin tool since 1999 ( over 15 years) and for the most part it has been an excellent entity-relationship (ER) design tool. Sure, it has it's issues, it seems to be lagging behind other enterprise ER tools, and is very expensive.

Moving forward, I wanted to see I could find a more affordable, and possibly free ER data modeling tool. CA does offer a free version of ERwin, but it is limited to 25 objects, far too small for anything other than classroom study. And the jump from free to well over $3,000 is too steep for most organizations.  So what to do if you have a need for a decent ER tool but don't have a big budget? There are some lower  budget tools for ER, Toad being one of the better names. And Toad offers a free version, but it's also limited, so we skipped it for this write-up. We decided our focus was to find a tool that was free while still providing the basic features needed for ER.  And since our primary focus is SQL Server - one that worked with SQL Server as well as the other major database engines. After doing some extensive searching, we found two that offer solid ER design features, reverse engineering from an existing database, comparison, and forward engineering.  There are many others, but if you only have time to look at two free options, these are the two to look at.

Oracle SQL Data Modeler data modeler

Oracle has long had the Java based SQL Developer with its data modeler capabilities, but Oracle also has the stand-alone SQL Developer Data Modeler.  It supports both logical and physical models, easy migration between the two, and an extensive list of properties for entities, attributes, foreign key relationships, indexes and more. It comes with core support for Oracle, DB2, and SQL Server, but also allows for the ability to add new data types to the target database. And since Oracle tends to have a more extensive list of features, the SQL Server features are well covered. See below for installation notes.

Open ModelSphere

Started by a team of professors and students at Laval University as an advanced CASE tool, ModelSphere has become an impressive modeling tool for ER, business process and UML modeling. Like Oracle's Data Modeler, it supports an extensive list of features for ER modeling. And like Data Modeler, it supports the addition of user defined data types so you can keep up with advances in your target database engine.  It comes either as a zip file or as an .exe file.  We setup the zipped version on a Windows 10 VM and the .exe version on an older Windows XP VM. You can find the users guide here: Open ModelSphere Users Guide.

Others to consider:

SQL Power Architect
SQL Power Architect is a free, easy to use tool for basic ER. And if you are looking for a good tool to start with, it might meet your needs. Overall the diagramming features were easy to use while supporting the most common Information Engineering Notation (a.k.a.  crow's feet).  Most of our work is with SQL Server, and this is where SQL Power Architect quickly stumbled.  The most recent SQL Server data types supported in SQL Power Architect are for version 2005. and unfortunately, SQL Power Architect (as of February, 2016) did not support user added data types. For our needs, this was a deal breaker. If and when SQL Power Architect supports user defined data types, this could be a nice tool to consider. Java based, so works in windows, OS X and UNIX/Linux.  You can find it here:  SQL Power Architect.

MySQL Workbench.

If your focus is with MySQL, Workbench is worth considering. Our focus is with SQL Server, so we took a pass.


I found another promising, but not free tool called DbSchema. it's $127/personal and $197/commercial. it has a long list of supported databases, including Cassandra, MongoDb and redshift. Along with all the usual db's.  It also provides a query editor and a random data generator. you can find it here:  DbSchema.

Setup Notes for Oracle SQL Data Modeler

You can find the latest SQL Developer Data Modeler here:
Since its java based, naturally, you first need to install the requisite 32-bit or 64-bit java kit.

To connect to SQL Server, you need to setup the SQL Server jtds jar file. You can find it on SourceForge here:  jTDS - SQL Server and Sybase JDBCdriver. The current file is We moved the files to the C:\Programs Files\Oracle folder.  From the SQL Data Modeler menu Tools | Preferences | Data Modeler | Third Party JDBC Drivers, add the .jar file you just saved. Once setup, you can connect to SQL Server.

Microsoft also has java drivers and you can find them here:  Microsoft JDBC Drivers 6.0 (Preview), 4.2, 4.1, and 4.0 forSQL Server.  As of now, we have not used these in SQL Data Modeler.

On another note, two ER tools that I have heard good things about, and are very cost effective are Toad's tool (mentioned above) and Sparx Enterprise Architect. both are $500 or less.

No comments: