Change, it's inevitable

I'm in the middle of changing jobs, I mean it literally. The last day for my old job was Friday, the first day of my new job is Monday, and it's Saturday night.

There is much to miss with my old position. I had the chance to participate in changing the core data strategy and the luxury of doing it with the right people. I can't stress this enough, the right people make the difference. I can't mention leaving without mentioning them because they are an incredible group.

When I announced my plans to move on, it was possible to promote from within because every had grown so very much. They are all brilliant and I will miss working with them.

So now I'm switching my role up a little bit. 

I'm moving on to more of a straight Data Architect position instead of Enterprise data. I have the chance to build out a number of interesting "cloud" products that will have several very serious, real world applications. 

There is a good bit of greenfield development and different agile techniques I expect to run into. I'll post as I learn new and interesting things. 

The State of Data Modeling Tools (Conclusion)

After reviewing a bunch of the data modeling tools out there, I've come to the conclusion that there are some great tools out there, some workable tools and some tools to avoid.

Data Modeling Tools Result Summary

• CA ERwin 
• This is a toolset that has been in the decline for while. CA is trying to pull it out of the nose-dive it's in but it has a legacy code-base without a lot of the original team around to explain it. As a long term system I can't recommend it. It will continue to go downhill.
• Visio 2010 Professional 
• Fine for small models but it will quickly become to unwieldy with a significant model. Microsoft doesn't seem interested in this aspect of the tool market and are not improving Visio for it.
• MySQL Workbench
• Stability is important. It's free but my time is not. I don't recommend this tool.
• SQL Power Architect (Community Edition)
• This tool needs better integration with the actual database. It has the most potential of any open source modeling tool I've seen.
• Embarcadero's ER/Studio Data Architect (Trial)
• This tool works flawlessly. If you can afford it, buy it. They've been doing this for over 15 years and have built an entire product line around modeling tools.
• Toad Data Modeler (Trial)
• I admit that the lack of stability irritated me greatly but I don't think that this product has improved much over the last few years and it may be on life support from Quest. Not recommended. 
• ModelRight Professional (Trial)
• For the price (roughly 25% of ER/Studio) this tool works amazingly well. The only thing it lacks is logical modeling, if you have one platform (like most folks) this is a great choice.

I hope that some of you found my research helpful. Your great comments help me remember to post.  :-)

ModelRight Professional

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

Background

I was talking to a co-worker who was at LogicWorks before they were purchased by Platinum Technology (who were later purchased by Computer Associates) and he mentioned that there was a new ERD company on the block, ModelRight. It turns out that some the original Logic Works folks put this together and I gave it a whirl. It's UI is a bit different but worth investigating further.


Stability

This ia another product I used frequently over the evaluation period. The small model I tested with (< 50 table) worked great and the application was fast and responsive even after many days of use. 

Price
ModelRight Professional: ~$1,095 ($895 + $199 maint)

ModelRight Professional for MySQL: ~$494 ($295 + $199 maint)


Features
Common Checklist

• Logic Modeling: No 
• Physical Modeling: Yes
• Reverse Engineering: Yes 
• Forward Engineering: Yes
• Compare against files and live databases. 
• Sub Models: Yes

The UI is extremely responsive, not only do you grab things when you are intend to, but all of the properties for the selected item are immediately visible. This is because the properties are not in windows that popup but are in a docked pane. I like this very much but it requires a bit more screen space, which makes working with it on a laptop awkward. After I got used to it this made working with the model unexpectedly more efficient.

Reversing out the DB worked as expected and the submodel management was intuitive. Just drag the entity you want to add to a submodel into the model pane and it was added. Slick, fast and efficient.

The compare and forward engineering created some of the most efficient scripts of all the modeling tools I've used. They actually demonstrated features I didn't know about on MySQL and worked very well. I don't think I had to re-write any of the generated scripts.

Conclusion

If you have a single DB platform, ModelRight is definitely a modeling tool to consider. It is the only one that actually made me more efficient than I expected. The SQL it generated was was excellent and it was rock-solid. 

Toad Data Modeler

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

Background

Toad is legendary for it's Oracle database management software. When I started at my current job I was asked to evaluate their SQL Server tooling. My evaluation was that the SQL Server tools were not fully baked and that Toad Data Modeler treated non-Oracle DBMS platforms as second class citizens. That was 4 years ago, Quest has put a lot of resources into the Toad family since then, lets see if Toad Data Modeler (TDM) has improved.


Stability

I used it for a few days to make my evaluation. The system only supports 25 entities in trial mode so I had to limited my model pretty severely. I didn't realize that I hit the limit until I was using it for a while, and it crashed. As soon as I hit the limit, the app stopped saving and hours got lost. 


Later on I used the application for a few hours and it crashed again. Luckily, because the model was kept small I was able to use my last save.


Price
Toad Data Modeler: ~$450



Features
Common Checklist

• Logic Modeling: Yes
• Physical Modeling: Yes
• Reverse Engineering: Yes 
• Forward Engineering: Yes
• Does not compare against the live DB, only against previous models. 
• Sub Models: Yes

The UI is reasonably responsive. 


Reversing the DB works ok but the arrangement leaves a lot to be desired.

The model compare and forward engineering works ok, but not great. Not being able to compare against a live DB leads to issues.

Conclusion

Toad Data Modeler is not stable. I can't stress the value of stability in tool selection. I really didn't enjoy using the tool and cannot recommend it.

ER/Studio Data Architect

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

Background

Embarcadero Technologies started off with making DB development and management tools. I ran into one of their tools (DBArtisan) in 1998 for managing my Sybase instances. At my next job I insisted on getting the full suite of Embarcadero Software. And my next job too. Unfortunately after that I wasn't able to justify the cost and it has been 7 years since I used the software. Did it age poorly, similar to what happened to ERwin? Let's see.


Stability

I used it nearly every day during the evaluation period, testing out a number of the different options. There were no problems with the small model I tested with (< 50 table) while it remained fast and responsive over many days being open and used. 

Price
ER/Studio Multi-Platform: ~$4,615.00 ($3,700 + $915 maint)

ER/Studio Open Source Databases: ~$1,360 ($1,100 + $260 maint)


Features
Common Checklist

• Logic Modeling: Yes 
• Physical Modeling: Yes
• Reverse Engineering: Yes 
• Forward Engineering: Yes
• Compare against files and live databases. 
• Sub Models: Yes

The UI is responsive, when you grab items they do what you expect them to. It's very similar to ERwin but the UI is much more responsive and stable.


After reversing out an existing database, the initial entity arrangement is surprisingly good, they have clearly put a lot of effort into it. There are several layout options and all of them work pretty well. Ultimately you still need to re-arrange the model a bit, but far less than any other tool I've used.

The Database compare and forward engineering makes very usable scripts that respect the integrity of the original model.

Conclusion

ER/Studio is an excellent product. There are add-ons to manage the model with a centralized repository and share database development tasks. There are also tools to establishing and maintaining conventions throughout your diagram.

In conclusion, ER/Studio is a great product, the price is high but it works flawlessly.

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.