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 http://www.oracle.com/sql 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 http://www.modelsphere.com/
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.
DbSchema
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 jtds-1.3.1-dist.zip. 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.
