Visio 2011 (ERD)

This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.

Background

Visio is the default business diagramming tool in most organizations. I've used it off and on for after-the-fact documentation but never to create the actual model. The newest version of Visio (2010) included a slightly remodeled UI and some tools to help with choosing colors (surprisingly, that is very useful to me). 


Stability
It's Visio, it works well for the small model I tested with (< 50 table) and had no stability issues. 

Price
Visio Professional: ~$400.00

Features
Common Checklist

• Logic Modeling: No 
• Physical Modeling: Yes
• Reverse Engineering: Yes 
• Forward Engineering: No
• Note, there is a plug-in created by Alberto Ferrari and hosted at CodePlex which does a non-comparing forward engineer of the DB
• Sub Models: Yes

Once you get past the Visio UI quirks, documenting an existing system using Visio is a breeze. Yes, extra clicks are required everywhere. Yes, the relationship link points are limited and not fun to reset. 

The initial arrangement of the entities is pretty bad, but after using SQL Power Architect and MySQL Workbench I can appreciate that the layout is not awful.

Microsoft has a team of writers maintaining the help system and there are several good web resources on building ERDs using Visio so there is plenty of available documentation.

The lack of a built-in forward engineering function can be worked around (as noted above) but it does not compare to the actual database. 

Conclusion

I have used Visio to document data models in the past and I maintain that it can do this at an acceptable level.

Also Visio can be a good way to get down some initial thoughts on a model if that is all you have available to you.

I do not recommend using Visio for ongoing, iterative data model development. Forward engineering without a comparison function makes this work too awkward. 

SQL Power Architect

This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.


Background

I must say that SQL Power Architect came out of no where for me. I was googling around when I stumbled upon it and it sounded too good to be true. A multi-platform, open source database modeling tool with an Enterprise version available (for pay naturally) if you need advanced features. Watching some of the videos it sounds like this was a tool developed in house that evolved into a product for the company. I couldn't find much more history than that, if anyone has some let me know and I'll add it in here.


Stability
I was back to using my small (< 50 table) model and had no stability issues. Rock Solid.

Price
SQL Power Architect: ~$00.00
SQL Power Architect Enterprise: $495.00


Features
Common Checklist

• Logic Modeling: Yes 
• Physical Modeling: No 
• Reverse Engineering: Yes 
• Forward Engineering: Yes
• Sub Models: No 

This is an interesting contrast to MySQL Workbench. SQL Power Architect insists that you do all of your work in the Logical Model (called the PlayPen Database) and generate SQL to apply to your Physical Model. Of course this gets awkward if you need to update your Logical Model from the actual database.


Once again Auto-Arrange is a very poor choice. So many of the relationships overlap that it is difficult to understand the model. 


There is no documentation for the Community edition available. To understand how this application works your best bet is to visit the YouTube SQL Power Channel and play around for a while. In all fairness the Enterprise Edition does have documentation available.


The GUI, well it is in Java, so it does not feel native. The scrolling is wrong, either it scrolls too fast or begins scrolling before you would expect it to. In most apps this is isn't a problem but as you're trying to work on your diagram this can get really awkward.


Conclusion
This is easily the best open source tool I've seen for data modeling. It is easy to setup, works with JDBC connections and allows you to compare and forward engineer your changes.


The downside is that all DB schema changes are expected to originate from it, or be added to the Logical Model manually. And the Logical Model does not support sub-models. And the GUI makes working with the models challenging.


These issues can be worked around by making a lot of small projects as sub-models. This raises it's own challenges with overlapping entities and synchronizing projects. 


All in all if you are working on an open source project with limited funds this may be your best approach but watch out, you will spend more effort managing this model than you expect.