Comparative Review: Sizing Up Data Modeling Software

Editor's note: At press time, Embarcadero Technologies released ER/Studio XE, the successor to ER/Studio Enterprise 8.5. Although there wasn't enough time to review ER/Studio XE for this comparison, it'll be reviewed in a future issue.

Data modeling software can help you get a picture of what your database environment looks like and what it contains. It can also help you design databases and generate code. Despite the sometimes hefty price tags of data modeling packages, they're true productivity tools because they significantly shorten the time it takes to perform tasks. Tasks that took weeks take only days, and tasks that took days take only hours.

Data modeling software typically requires a long learning curve because of the complex capabilities built into it. But a good data modeling software package should have a relatively straightforward "getting started" path. It should also provide most, if not all, of the following features:

Using these features as criteria, I recently tested six popular data modeling packages:
 

Most of the data modeling packages I evaluated have the core features I just mentioned. If a product doesn't have a feature or if it has a special extended feature, I note it in the individual reviews that follow. I also created a chart that compares the six products' features. Table 1 contains that chart.

Note that each vendor has its own nomenclature when referring to the components in their software. To simplify the product evaluations, I use generic terms (e.g., tree view, workspace) in the individual reviews and the product comparison chart.

Toad Data Modeler for SQL Server 3.5, Xpert Edition

Installing Toad was incredibly easy and straightforward. However, the Toad installation dumps everything on the C drive. It would be better if the user could decide on the default file location.

Toad uses the conventional data modeling layout—that is, a tree view on the left, a message area at the bottom, and a workspace that takes up most of the screen's real estate. In Toad, the tree view contains multiple tabs, with each tab containing a project that you've brought into your workspace, plus the various artifacts that belong to the project (e.g., file groups, partition schemes, entities and attributes, relationships, views). The tabbed workspace is where the data model is displayed and edited. The message area contains two small windows: Application View and Message Explorer. Application View is a brief replicate of the tree view. It lets you quickly switch from one view of the model to another by means of a tab. Message Explorer is the log file. It displays errors, warnings, and any other messages that Toad needs to relay to the user.

Toad has some neat features, such as a place for notes in the tree view. You can literally build a punch list of things to do, which is necessary because of the numerous steps involved when creating a new database or refactoring an old one. Toad has a lot of right-click menus that are context-sensitive. Those menus and keyboard shortcuts make repetitive actions fast and easy.

Toad doesn't have a centralized repository for team development, but it does have version control. You need to be in Expert Mode to use versioning, and only projects can take part in the version control system. Shifting Toad into Expert Mode also lets you write and edit scripts, customize package features, and do other advanced tasks.

One productivity feature I really like is the rollover highlighting of primary-to-foreign-key relationships. When you place the cursor on a relationship line, the parent and child columns in the related tables are highlighted—no guessing or searching needed. Managing the model in the workspace is easy and straightforward. For example, getting relationship lines untangled is as simple as right-clicking, selecting Straighten All Lines, right-clicking again, and choosing Change Lines to Right Angle. No data modeling package that I know of produces a compact auto-layout, but Toad's screen management makes re-organizing and re-orienting artifacts easy.

Color-coding of the keys is another feature that I really like. A red key indicates a primary identifier/key. (There's no problem with identity properties, as the property window is fully compliant with SQL Server.) A green key indicates a foreign key.

Toad isn't perfect, though:

Toad is available in the Professional, Xpert, and Development Suite editions. It's also offered as a freeware package, which is limited to a maximum of five users within an organization.

DatabaseSpy 2011, Enterprise Edition

Installing DatabaseSpy was straightforward, but I had to connect to a live SQL Server machine as part of the startup process. For this review, I was connecting to a remote server with a SQL Server logon. I was prompted for both local and remote credentials, which got a little confusing until I figured out what DatabaseSpy wanted.

DatabaseSpy suffers from an identity crisis. It's like SQL Server Management Studio's (SSMS's) Database Diagrams tool (aka the SQL Diagrammer) combined with Red Gate Software's SQL Compare and SQL Data Compare, plus a report builder. In DatabaseSpy's UI, there are various tree views that resemble SSMS and Visual Studio. The workspace, which is used for editing the database tables, resembles the SQL Diagrammer. There's a separate overview window, which lets you pan the workspace, and a query window, which you can use to query live data. Like the SQL Diagrammer, you're connected to a live database, so changes made to the schema are immediately reflected in reality.

The entire DatabaseSpy model is loaded into memory, which results in speedy editing and display. However, this can be problematic if you have large databases (large in the sense of having many objects in the schema) or a client computer with limited memory. While the load wizard is working, you can't access the Help files or any other DatabaseSpy feature because the load wizard is modal.

DatabaseSpy supports only the physical model, so you can only edit existing objects or add new objects to the database that you're connected to. Because you're connected to a live database, you can inadvertently modify tables and columns and cause chaos (or worse) in a development or production system. I found other problems as well:

DatabaseSpy comes in two editions: Enterprise and Professional. Both are available in 64-bit versions at no extra cost. You can purchase DatabaseSpy separately or as part of a package called the MissionKit 2011 for Software Architects.

DeZign for Databases 6.2, Expert Edition

Installing DeZign for Databases was very easy and seamless. After you plug in the license code and key, the DeZign application opens with a sample entity relationship diagram (ERD) in the workspace, giving the experienced user a good first impression and the new user a sense of place and purpose. DeZign is set up in the conventional data modeling layout.

I was struck by the high comfort level I encountered with DeZign. The visual query builder for views makes creating views a snap, and the ability to set object naming standards (and other options) by project is wonderful. Generated code is arranged in correct hierarchy order. For example, the code deletes any constraints before deleting the tables to which they apply.

There's also a high level of ease in managing models. For instance, there's a function that lets you search for and replace data types across a model, which makes data-type management a snap. And a drag-screen feature makes it easy to move around in a large model.

Switching platforms (e.g., SQL Server to Oracle) is very easy, but it comes with a caveat: The code that's going to be generated will be in the dialect of the original platform. DeZign has a few other drawbacks as well:

DeZign comes in three editions: Standard, Professional, and Expert. The Expert Edition is the version for both data architects and enterprise architects. (Want to know how these two positions differ? See the web-exclusive sidebar "What's the Difference Between a Data Architect and an Enterprise Architect?") You can purchase DeZign separately or as part of a package called the DBTools Bundle Expert Edition.

ER/Studio Enterprise 8.5

ER/Studio Enterprise is a big download file, so make sure you have a lot of room on your local disk before starting the process. During the install, you have to set up a user account on the Embarcadero Technologies website.

ER/Studio has so much flexibility and so many features that it'll take years for anyone to grow into it. Data architects who need to maintain strong standards will love the domain control built into it. When you create an entity, you can choose from a prepopulated data dictionary of domains instead of creating every attribute from scratch. The data dictionary contains standard, re-useable attributes similar to user-defined data types, which have the same data type/length, definition/meaning, rules, and constraints, no matter where they're used in the model.

The software uses the conventional data modeling layout. There are multiple ways to navigate large models. The Overview screen gives you a birds-eye view of the entire model. The Pan feature lets you pan the model directly. The Zoom window enlarges whichever artifact your cursor is passing over.

ER/Studio generates both Data Manipulation Language (DML) code, which creates the database schema, and DDL code for selects, inserts, updates and deletes, with triggers to handle updates and deletes of a parent with children and children without a parent. ER/Studio produces the cleanest generated SQL code of all the products I reviewed. In my test, the DDL code executed without a single change required.

There are a few things about ER/Studio that bug me, but not many:

ER/Studio Enterprise is a productivity suite that contains five ER/Studio software packages: Data Architect, Business Architect, Software Architect, Portal, and Repository. Data Architect and Business Architect can be purchased as standalone products, as can several ER/Studio add-ons. The evaluation copy I received shipped with a central repository and a user portal, which I didn't review.

ERwin DM 7.3

Installing and setting up ERwin DM was easy, although the download took a long time. ERwin employs the conventional data modeling layout. 

Features such as the data-type standards editor and the report template builder add much value to this package. For the busy data modeler, being able to view all the instances of a single type of artifact (e.g., all tables, all columns) in a single list and being able to check or edit their common properties is a terrific timesaver. Coupling ERwin DM with its Saphir option can help anyone struggling with extracting metadata from popular third-party ERP packages.

When creating logical models, you can use the conventional logical data types (e.g., BLOB, string) or more detailed, platform-specific data types (e.g., tinyint, int). You would use logical data types if you planned to push your logical model to more than one type of physical platform, such as pushing it to SQL Server and Oracle. The SQL query tool is handy if you have to view the data in a connected database. Rather than using SSMS, you can view it directly in Erwin DM. The model comparison wizard is one of the cleanest and easiest to work with that I've found.

ERwin DM doesn't support any version of SQL Server prior to SQL Server 2000. The workaround is to script out the database into a DDL file, then reverse engineer from that file. I couldn't dispose of models without first saving them to disk, then using WinExplorer to delete the file, which seemed unnecessarily convoluted. Other annoyances and problems include:

CA offers the Erwin DM Community Edition, a freeware version of ERwin DM. CA also offers several other modeling products (e.g., ERwin Data Model Validator, ERwin Model Manager) that you can purchase.

PowerDesigner Studio Enterprise 15.2

The PowerDesigner installation DVD comes with a set of video tutorials that really help the new user get started. (Even existing customers who upgrade will find the tutorials helpful because there are so many new features in version 15.) Installation was a non-event. PowerDesigner uses the conventional data modeling layout.

The newest version of PowerDesigner offers enterprise architecture modeling, with support for multiple enterprise architecture frameworks such as the Zachman Framework, The Open Group Architecture Framework (TOGAF), and Federal Enterprise Architecture Framework (FEAF). Plus, you can build your own custom framework. Enterprise architecture models let you map all organizational systems and processes, aligning stakeholders from all parts of the business (and not just technology) with strategies and goals. You can organize models by project, create shortcuts for documents, and relocate documents all from the PowerDesigner UI. You can link models within a project.

In addition to all the conventional modeling notations, PowerDesigner strongly supports the object-oriented (OO) world. For example, the OO model supports class, object, use case, collaboration, sequence, statechart, activity, component, deployment, and composite structure diagrams.

PowerDesigner has strong large-model management capabilities as well. For example, you can divide model components (e.g., table artifacts, relationships) into packages, which are a form of submodel. Packages can be nested. File and artifact management is extremely easy. You can use a form for creating and editing tables and their associated artifacts (columns, keys, indexes), or you can use the grid-like list layout, which is extremely customizable. There is a centralized repository, with check-in and check-out capabilities. The model's find operation automatically presents the target artifact in close-up mode, so it's easy to manage.

Individual or organizational preferences, such as display preferences for the model, are easily modifiable, with a large library of options. There's strong support for domain data typing. You can create data domains and use them across all model levels. There's equally strong support for modeling standards, which are set by default to industry best practices, but they're completely customizable. The impact analysis capabilities (both cross-model and lineage) are amazing. After you've modeled the enterprise, you can run an impact analysis to display downstream and upstream impacts of a change across the enterprise.

The report wizard makes report generation quick and straightforward. The generated reports are hyperlinked—the metadata links to the relevant artifact and vice versa. Reports are saved with the model, after which you can edit them in the Report Editor. Report templates are customizable.

In the requirement model, artifacts can be linked to other model artifacts for traceability. The optional repository, which is a central storage location for all models, is secure. A login is required, and individuals or groups can be assigned specific permissions. You can browse through the artifacts in the repository and edit them after extraction. Conflict resolution management upon check-in lets you reject, overwrite, or merge changes from multiple modelers. There is a web-based user portal for the repository and a search engine.

Even this most elegant of data modeling software has faults:

PowerDesigner is the only product of those reviewed that goes beyond data analysis and modeling. It reaches out to the enterprise and lets you model the organization. In addition, you can tie documents (static and otherwise) and databases into the organizational framework model. PowerDesigner is by far the most complete organizational modeling package on the market. Right out of the box, you can model business communications, city planning, organization charts, process maps, application architecture, service-oriented designs, and the technology infrastructure in your shop, with no need to purchase additional products.

Editor's Choice

All the data modeling packages I reviewed aim to ease your workload and help you create a better work product deliverable. Some achieve these goals better than others. The product that will work best for you depends on your job duties and your budget.

The reviewed software falls into two general categories:

PowerDesigner Studio Enterprise, DeZign for Databases, ER/Studio Enterprise, and ERwin Data Modeler are classic packages, whereas Toad Data Modeler for SQL Server and DatabaseSpy are lighter-weight products.

For DBAs and developers, I recommend Toad Data Modeler, despite my struggles with file management. The package is intuitive, so it's easy to learn and use. It's also built to enforce modeling best practices, so it'll help grow your modeling skills. Besides, it's just plain fun to use.

For data architects, I recommend one of two products. If your primary driver for choice is budget, go with DeZign for Databases. It has tons of capabilities with a high comfort and productivity factor, and is clearly a best buy. However, if your primary driver is standards enforcement, go with ER/Studio Enterprise. This is the most powerful software package for data architects that I evaluated.

For the enterprise architect, I recommend PowerDesigner Studio Enterprise. It's an elegant product with numerous capabilities, which is why it deserves the title of "Editor's Choice." It'll probably be a few years before you learn how to take full advantage of it, but during that time, it'll guide you into using industry-standard best practices and help you hone your enterprise architect skill set.