Data modeling tools: a comparison for enterprise vs department use

In a prior blog, I examined data modeling and data modeling tools at a very high level. I answered basic questions such as what is data modeling, why should I care, and what capabilities should data modeling tools offer? I also broke down the major types of tools into two markets: departmental or project-oriented versus enterprise-oriented, with several examples for each market. In this blog, I’ll highlight and compare data modeling tools for each market.

To recap, if your requirements are relatively simple and your budget is constrained, then a lightweight entity relationship diagram (ERD) tool may well meet your needs. This category of data modeling tools is best suited for project or departmental usage, but not for enterprise wide deployment. For example these tools generally don’t have centralized repositories in databases with concurrent model check-out/check-in and conflict resolution. Moreover, they often offer support for just one or a few of the ERD modeling notations. But if you don’t need advanced capabilities. then ERD tools may well be sufficient. Here’s a short list (in order) of tools that I feel are best of breed, lightweight data modeling tools:

• Toad® Data Modeler by Quest®(formerly CASE Studio)
• Oracle SQL Developer Data Modeler
• DBWrench
• DBSchema
• XCase

Let’s compare these tools in more detail.

Although these comparisons are close, I suggest that the three with a fully-functional and bi-directional compare/sync are worth consideration. However as much as I really like its GUI style, DBSchema Pro’s compare/sync was not 100% accurate – that’s a deal breaker for me. That leaves just Toad Data Modeler and Oracle SQL Developer Data Modeler as serious, lightweight data modeling options. Generally, I recommend Toad Data Modeler due to the number of databases it supports and that Quest provides full technical support as part of the modest purchase price. Here’s an example of an entity relationship diagram in Toad Data Modeler.

Figure 1: Toad Data Modeler

Since I personally value compare/sync capabilities so highly in a data modeling tool, here’s an example from Toad Data Modeler. Note that it clearly shows that there is an entity that contains attributes not yet in a database table, and a database table that contains columns not yet in a data model entity. Being able to see such differences on either side of the comparison is critical, because if the data model and database are even just slightly different, then the database’s effectiveness, efficiency or both have been compromised. I see the data model much like the source code used to create a program executable, where one is the result of the other.

Figure 2: Toad Data Modeler Compare & Sync

If your company is instead looking for an enterprise wide data modeling tool where data models can be centrally maintained, budget is more flexible and support is a key issue, then here’s my short list of tools that I feel are best of breed, enterprise data modeling tools:

• erwin® Data Modeler by Quest® (probably largest familiarity and install base of any tool)
• Idera ER Studio
• SAP PowerDesigner (formerly Sybase)

Enterprise data modeling tools must possess every feature of lightweight data modeling tools (except low price), plus add significant features above and beyond. erwin Data Modeler provides“complete compare,” a feature that essentially define the industry standard for what compare/sync should offer. I can honestly say that no data modeling tool comes even close to the GUI visual presentation style and overall function of complete compare.

Also critical to an enterprise data modeling tool is a central repository that permits collaborative modeling at every level: model, sub-model and down to the object level. Moreover erwin’s centralized database repository technology has been proven to scale to hundreds or thousands of users. I find that the only legitimate competition to erwin Data Modeler comes from Idera’s ER Studio. However, I have found ER Studio’s compare/sync neither as robust nor visually appealing as erwin, and its database repository does not scale well with a high number of user or objects. Hence, I consider erwin to be the single best enterprise data modeling tool bar none.

Related information:

Blog: Data modeling for the business analyst

Blog: Toad Data Modeler Reverse Engineering & Convert Model Wizards

More Toad Data Modeler blogs

IDC Datasheet: Quest Software Acquires erwin Inc.

Visit Quest erwin for more information about an enterprise data modeling solution.

Blog: What is data modeling and which tools are the best?