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:
- Ability to capture and define business terms (i.e., a glossary)
- Ability to capture and document business requirements
- Ability to capture and document business processes
- Support for multiple database platforms (not just SQL Server)
- Support for two levels (minimum) or three levels (optimum) of data modeling (i.e., conceptual, logical, and physical)
- Error checking on all model levels, based on industry standards for modeling best practices
- Ability to modify best practices to fit individual corporate standards
- Ability to customize and modify model templates to fit corporate presentation standards
- Ability to customize the file management system to fit corporate or personal needs
- Ability to reverse engineer, document, and inventory existing databases
- Ability to verify a model by comparing it to stored model standards, prior to generating SQL Data Definition Language (DDL) code
- Ability to forward engineer a database (i.e., create a new database or refactor an existing one), either by generating SQL DDL code or by creating a live database using ANSI-standard SQL code or the appropriate SQL dialect for the target database platform (user's choice).
- Ability to customize the structure of generated DDL code, such as defining keys and constraints that are internal or external to a table definition
- Ability to define and code file groups, partitions, users, roles, and other infrastructure artifacts
- Ability to define and code tables, columns, defaults, constraints, and other database artifacts
- Ability to define and code indexes, stored procedures, triggers, user-defined functions (UDFs), and other code-based artifacts
- Ability to compare database models at the physical level to map the differences between them
- Ability to share models in a team environment
- Ability to generate reports in multiple formats for publication and sharing
- Documentation in different types of media (e.g., print, online, video)
Using these features as criteria, I recently tested six popular data modeling packages:
- Quest Software's Toad Data Modeler for SQL Server 3.5, Xpert Edition
- Altova's DatabaseSpy 2011, Enterprise Edition
- Datanamic Solutions' DeZign for Databases 6.2, Expert Edition
- Embarcadero Technologies' ER/Studio Enterprise 8.5
- CA's ERwin Data Modeler (DM) 7.3
- Sybase's PowerDesigner Studio Enterprise 15.2
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.