ER/Studio 4.02a

Tree view brings a new perspective to data modeling

Embarcadero Technologies is pitting the latest major release of its data-modeling tool, ER/Studio, against Computer Associates' (CA's) PLATINUM ERwin/ERX, the current market leader for data-modeling tools. Data modelers can be very loyal to their tools, and ERwin, which has been around for more than a decade, is a high-quality product. Unseating ERwin as a market leader won't be easy. However, ERwin has problems with SQL Server 7.0 data types—problems severe enough that database designers modeling for SQL Server will want to look closely at an alternative such as ER/Studio.

ER/Studio is part of a suite that includes DBArtisan 5.1, a database administration tool, and Rapid SQL 5.5, a database development tool for various SQL dialects. Embarcadero positions ER/Studio as a tool for rapid design that can reduce data-modeling time and help automate database design. Accordingly, ER/Studio pioneers a new interface that eases the management of logical and physical data models and includes a VBA-like template language for automatically generating triggers and stored procedures. As of this writing, Embarcadero's Web site provides an upgrade from ER/Studio 4.0 to ER/Studio 4.02a, which contains a set of bug fixes for the tool but no additional functionality. I reviewed the 4.02a upgrade.

One of the more difficult aspects of data modeling is coordinating the logical and physical versions of an entity-relationship model. ERwin 3.51, for example, lets you toggle between the two views, but remembering where you are in a large model can be daunting. ER/Studio uses the familiar tree view interface to solve this problem.

ER/Studio's tree view lets you navigate between one logical model and one or more physical models. In a logical model, you abstract away from implementation details—such as database management system (DBMS) platform, specific data types, nullability, defaults, constraints, table triggers, and stored procedures—so you can focus on the design of database entities, their attributes, and the relationships among them. In a physical model, you choose a particular target DBMS and apply implementation details to the attributes and the tables. Because ER/Studio lets you have multiple physical models, you can try different methods of implementation or implement one design on several physical platforms.

Within a given physical model, you can also create submodels. With submodels, you can separate various subsets of a large physical model, including tables and views, to make parts of the model more understandable. To illustrate ER/Studio's submodel feature, I reverse-engineered the SQL Server 7.0 Northwind sample database into an ER/Studio model and included both tables and views. Northwind has many views, and seeing them with the tables in one diagram can be confusing. So, I created one submodel for the tables and one submodel for the views and their tables, which gave me an easier-to-understand data model. Figure 1, page 60, shows the result.

ER/Studio's tree view lets you easily manage designs with multiple models, but the interface comes at a price. The tree view doesn't leave much screen room for the model. You can shrink the tree control to the left and just use the toolbar for designing, but to select a different model, you still have to expand and navigate the tree view. Unlike ERwin, ER/Studio doesn't offer a pull-down control inside a toolbar to switch from physical to logical model and back again.

To handle complex models with many tables, you can use a zoom window to see a magnified version of the model as you pass over it with the mouse cursor. The zoom window lets you see details while keeping all or large parts of a model on screen. If your system supports dual monitors, you can put the zoom window on one monitor's screen and navigate your model on the other monitor's screen. Figure 1 shows an example of the zoom window under the tree view.

For synchronizing logical and physical models, ER/Studio has a powerful and intelligent Merge utility. You can merge any physical model into the logical model or propagate from the logical model to a particular physical model. You can select the comparison level, and a dialog box will show the differences between the objects. As with ERwin, you can select a subset of the differences to migrate either to your model or to the database.

However, in the display arena, ER/Studio still needs some work. ER/Studio doesn't handle connector lines between entities as smoothly and flexibly as ERwin does. With ER/Studio, you can change the line properties for each model from elbowed lines to straight lines and back, but you have little control over line placement. Embarcadero says it plans to enhance ER/Studio's display capabilities in a future release.

Key to Embarcadero's strategy of automating the database design process is ER/Studio's use of Sax Basic, a Visual Basic for Applications (VBA) clone. Sax Basic automatically generates triggers and default stored procedures for insert, update, and delete operations. You can find Sax Basic's template code and libraries in the tree view's Procedural Logic tab. Sax Basic is VBA-like, even down to the debug window, as Figure 2 shows. Within the code, you access entity objects' properties to construct a T-SQL string. For example, CurrEntity.TableName returns the table name, whereas CurrEntity.ParentRelationships returns a collection of all the current entity's parent relationships.

Modelers devoted to ERwin's template language, however, will likely find ER/Studio's Sax Basic templates disappointing. Unlike ERwin, ER/Studio doesn't let you attach individual templates to tables or quickly see the generated T-SQL code. ERwin's approach to presenting the generated code is unusual; you press the Tab key inside a template editing session to bring up another window with the generated code. But being able to inspect your generated code almost immediately is convenient. With ER/Studio, you have to generate the entire database creation code to see the results.

ER/Studio imports and exports models to SQL Scripts or XML. Although the product imports ERwin .erx files—the text-output version of an ERwin data model which has become a standard data-modeling file format—it doesn't export them. When importing an .erx file, ER/Studio 4.02a tries to import the ERwin templates but fails on a couple of points. ER/Studio loses ERwin's attachment of templates to particular tables and tries to convert the ERwin template language into Basic templates. These converted templates still contain the ERwin language, but the templates aren't valid Basic, so you can't use them for any practical purpose. Embarcadero says it intends to add full ERwin template language compatibility in a future release.

ER/Studio, however, has one significant advantage over ERwin that could prove decisive for SQL Server database designers. Unlike ERwin, ER/Studio correctly handles all SQL Server 7.0 data types. ERwin 3.51, the most recent version of the tool as of this writing, can't consistently handle the Unicode fixed-character data type. You can create a physical model with the nchar data type and forward-engineer the database from the model, but if you reverse-engineer the model from the database or try to synchronize the model with the database, ERwin 3.51 turns nchar data types into fixed char data types. This problem can prove a fatal flaw if you're designing an internationalized database. In addition, ERwin doesn't support bit columns that allow NULL, either in reverse or forward engineering.

The fact that ERwin doesn't correctly handle two fairly common SQL Server 7.0 data types means that you can't keep an ERwin model and a SQL Server 7.0 database synchronized if you use those data types. (According to CA's help line, ERwin's problem with nchar is a listed bug. As of this writing, the company had no projected date for fixing this problem.) In case you think ER/Studio doesn't have any data type problems, note that neither ERwin nor ER/Studio successfully reverse-engineers computed columns. Both reverse-engineer computed columns as the resulting numeric data type, and their respective attribute dialog boxes don't let you define an attribute as computed. Embarcadero says it plans to support computed columns and SQL Server 2000 indexed views in a future release.

ER/Studio lets you associate many physical data models with one logical model and provides an intuitive interface for navigating the models. In addition, the tool's ability to correctly handle all SQL Server 7.0 data types is a decisive advantage for designers who need to synchronize their models with SQL Server databases. To win over ERwin users, however, Embarcadero needs to improve ER/Studio's screen aesthetics by providing more flexible entity relationship lines and enhancing the product's template capabilities.