Document Your Database

Welcome Guest. | Log In| Register | Membership Benefits

November 10, 1997

Document Your Database

A number of packages on the market let you reengineer your database for n-tier applications

s you reengineer that legacy application, you're probably wondering how the host database was designed. You may also wonder who designed it-thinking of ways to punish them-but that's an article for Soldier of Fortune magazine. For now, you need a tool to document your existing database and help you reengineer it for your new n-tier applications.

A number of tools are available, including market leader ERwin/ERX from Logic Works and Embarcadero Technologies' ER/ Studio. Others include Sybase's PowerDesigner, Popkin Software and Systems' SA/Data Architect, Oracle's Database Designer, Visible Software's Visible Analyst Workbench and EasyER, InfoModelers' InfoModeler, and the combination of Visio's Visio Professional and A.D. Experts' 4Keeps add-in.

All these packages let you diagram your database tables, fields, keys, and relationships. They also let you automatically create the actual database. They su pport numerous databases via Open Database Connectivity (ODBC) drivers and let you reverse-engineer an existing database into a physical model.

Some of the products support creating separate logical and physical models. The logical model provides a normalized form of the database that is not tailored to the limitations of any specific database-management system. The physical model incorporates DBMS-specific information, such as the actual data type. For products that let you create both models, creating the physical model from the logical model is a simple menu pick.

The price range for these Windows products is huge: The Visio Professional-4Keeps combination is priced below $600, and ERwin/ERX tops out the range at $3,495. For some data-modeling and documentation tasks, the difference in price is not worth it. The less expensive products are just as good at database documentation tasks as their more expensive rivals. The higher price usually pays for extra modeling tools. Amazingly, though, for all the money you might spend on some of these packages, the lack of basic reporting features is appalling. For those products that cannot place a diagram into a report, you must use either the Windows copy-and-paste feature or create a Windows metafile from the diagram and then manually import the information into your word-processor document.

To test these packages, I reverse-engineered an Informix SE version 7.1 database with 37 tables and several stored procedures. I made minor changes to the resulting model to test out each product's usability. The resulting model was also incorporated into a Microsoft Word document for publishing. The packages were all tested on a 100-MHz Pentium system with ample disk space, 32 Mbytes of RAM, and a dial-up connection to an Informix server. Any unique performance issues encountered are noted in the reports below.

ERwin/ERX 3.0
Numerous additions are packed into the newest version of ERwin/ ERX. The biggest change is the separation of the logical data model from th e physical database model, allowing you to invent the data model with generic data types and then map these generic types to actual data field types. For example, in the logical model, a data item could have a logical type of ZIP code, which translates to the physical types of CHAR(9) on some databases and VARCHAR(9) on others.

Also new for this release is support for database views and improved reporting. The package also includes support for automatically transforming many-to-many relationships into a form that a database can handle. A tool to synchronize and selectively update your database or your model based on differences between them is also included, as is default index naming that uses a specialized format that you design.

Notations used to create your logical and physical models are the IDEF1X and Information Engineering (IE) methods. Fourteen DBMSs are supported, along with six desktop databases. Data-model verification is not provided with ERwin/ERX, so you have no way of knowing if your datab ase is properly normalized.

ERwin/ERX 3.0 imported my Informix database, and the resulting diagram was well organized as trees of related tables with no overlapping objects. ERwin can create text reports as well as create an OLE link to Microsoft Word 97, if you have it, to create a formatted report. ERwin/ERX does not provide a way to include your model diagram in the report.

PowerDesigner 6.0
Recently renamed from S-Designor, the PowerDesigner series of tools lets you document the business process and convert information from the business process into a logical data model. From the logical model, you can generate a separate physical data model, which can then be used to generate an actual database as well as data-bound forms for PowerBuilder, Visual Basic, Delphi, Power++ (which was recently renamed from Optima++), or a Web site. These many features come at a price of about $5,000. For just the part that will let you create logical data models, generate physical databases, and reverse-engineer existing databases, you will need the PowerDesigner DataArchitect, which is priced at $2,495.

DataArchitect only lets you create models using the IE modeling notation. But support for databases is extensive, not only for the common ones, but also for obscure ones such as Adabas D, Empress, and SuperBase IV. If you're working with a data warehouse, then you will want to get WarehouseArchitect. It'll set you back another $4,995, but it provides warehouse-specific features, such as support for high-performance indexing schemes and database partitioning.

Reporting with the entire PowerDesigner line is exceptional. It can create report templates as well as export the resulting report into Rich Text Format, which is easily incorporated into almost any popular word processor. Diagrams can be included in the report as well.

I discovered two flaws during my DataArchitect tests. One was the inability to properly handle certain data types. DataArchitect wanted to change 2-byte integer values to 4-byte integers. A dditionally, diagrams resulting from a reverse-engineered database were not well organized, and it required some manual labor to redo the diagram's layout.

SA/Data Architect 4.5
Like PowerDesigner, SA/Data Architect (SA/DA) provides features for setting up the architecture of your database as well as of your data warehouse. SA/DA also lets you generate Visual Basic, PowerBuilder, or Delphi data-bound forms from your data model. These features would cost more than $7,000 with the PowerDesigner product line, but SA/DA includes them for $1,895.

SA/DA places all models and shared data definitions in a common "encyclopedia," which is a repository directory. Links to Intersolv's PVCS version control are also available with SA/DA, providing enterprisewide version control for your models.

SA/DA supports both logical and physical models, and lets you create multiple physical models from a single logical model or a segmented portion of it-one for each DBMS you plan to create from your logical model. Lo gical and physical modeling notation support is extensive, including IDEF1X, IE, Unified Modeling Language (UML), OMT, and Booch. Model verification is provided and visually marks your diagram with problem emblems. The SA/DA user interface is more cluttered than the other packages evaluated for this article; you should definitely consider using a 17-inch monitor or larger for creating your documents with this package. SA/DA performance also felt sluggish on my PC.

Unlike the rest of the SA/DA application, the reverse-engineering feature of SA/DA is a separate, 16-bit process called Sarde, which had serious problems with my ODBC 3.0 driver for Informix. When other 32-bit ODBC applications were running, Sarde crashed. Without other applications running, Sarde completed the task only if certain database features, such as Defaults and stored procedures, were not imported. Once the database was imported, SA/DA did a fair job of organizing the symbols to limit the amount of overlap; however, it was unable to elim inate all overlapping symbols.

SA/DA supports a standard set of reports as well as user-definable reports. For exporting reports to other documents, you have only two choices: an unformatted text file or an HTML file. You have more flexibility in the types of unformatted text reports. The HTML report generator, which is a separate process, is a slow-running memory hog. You can also import your diagram and data definitions into Microsoft Word 95 or Word 6.0, but not Word 97, via a macro that you run from Word.

InfoModeler 3.0
With more of an object-oriented flavor than the other products in this review, InfoModeler Enterprise Edition supports not only IDEF1X and Relational, but also the Object Role Modeling (ORM) notation. ORM displays information about your data from the perspective that your data entities are objects that have roles within your design. ORM also makes the assumption that a properly normalized database automatically flows from the way you describe your objects.

InfoModeler supp orts only the creation of logical models and then creates the actual database from that model. Physical modeling is not supported. If you plan only to operate against a single type of database with your model, then this limitation shouldn't bind you too badly.

Against my test database, the InfoModeler reverse-engineering wizard had no trouble extracting my database objects. After selecting all the tables I wanted to import into my model, InfoModeler obtained the information, but did not place the resulting entities into the model. You must create your model pages and manually place the tables onto each page as desired. An auto-layout feature is available, but it's prone to overlapping your entities.

Reporting is as easy but not as complete as with PowerDesigner. You can precisely configure the information you want to place into your report using a report wizard. The completed report is then run against your model, and you can print the report or store the results in a Rich Text Format file for inclusion i n a document created with your favorite word processor. Unfortunately, none of the reports lets you embed the model diagram.

Visible Analyst Workbench 6.2
Like System Architect and PowerDesigner, Visible Analyst Workbench (VAW) provides many more features than just database modeling and documentation. Support for business-process modeling and building state transition, flow charts, and structured design diagrams is also included. Visual Basic and PowerBuilder application generation are included in the VAW for $2,995.

VAW supports the Chen, Bachman, and IE data-modeling notations. Normalization and syntax checking is included. Automatically generated entity-relationship diagrams are well organized, with no overlapping relationship lines or entity boxes.

The downside to VAW is that it is confusing. Among its many user-interface sins, numerous dialog boxes are used to define data attributes, and menu choices are not clearly labeled.

As for my test, VAW was able to import my Informix database an d create a nicely formatted diagram. Text-based reports are possible, but VAW has no native way to export the diagram directly into a file format suitable for a word-processing document.

Database Designer 1.0
Although Oracle Database Designer (ODD) is a version 1.0 product, it's not entirely new. Much of the code for this product came from Oracle's Designer/2000 product. For $995, you get a good physical modeler with no bells or whistles.

With ODD, you only get to create your diagrams using IDEF1X notation. Auto-layout is possible, but the resulting diagram can still end up with entity blocks overlapping relationship lines. Database verification is impossible, as are any complex reports. After changing the model, you can generate a database with it.

As for the tests, the program imported the Informix information quickly and easily. But since no reports are available, ODD provides no way to incorporate information about your design into a document-unless you consider a file containing hundreds of lines of SQL code to be descriptive. If you want the additional capabilities, then the models you create are compatible with Designer/2000, which is priced at $5,995.

EasyER 1.21
Visible Systems recently merged with Evergreen Software Tools and inherited its "Easy" line of CASE tools, including EasyER, which is a low-cost data modeler. EasyER 1.21 not only provides physical modeling for your database, but it also has an application generator to help you create starter PowerBuilder, Visual Basic, and Delphi apps.

Visible recently released EasyER 1.50, priced at $695. It doesn't include the application generators, but the Professional Edition, priced at $995, does include them.

The modeler is missing a few usability features that I wanted, most noticeably auto-layout and multiple-object alignment. However, it supports several modeling notations, including IE, IDEF1X, UML, OMT, Coad-Yourdon, and Bachman. Model verification is not provided. After reverse engineering a database, the objects a re placed into the diagram window without attributes. Once you choose to display the attributes, the objects end up overlapping each other and need to be repositioned. Repositioning these objects is a pain without auto-layout.

Reporting is good, provided by Crystal Reports 4.5. A number of useful reports are included with the application, any of which can be formatted into a Rich Text Format or Word for Windows file. But you cannot incorporate your model diagram into a report.

Importing my test database worked fine until the program tried to import the stored procedures, causing the entire application to crash. The objects that had been imported to that point remained in the data dictionary without corruption. EasyER's data dictionary is kept in a Sybase SQL Anywhere database. By using a real database engine, your EasyER data is less likely to corrupt, but performance is slow.

ER/Studio 2.0 (beta)
Like ERwin, ER/Studio is also being upgraded. Highlights for this release include separation of logical and physical models, the ability to "round-trip engineer" a database design, improved database support, and improved usability.

With ER/Studio, you get a layout editor, minimal auto-layout, support for only the IDEF1X notation, data model validation for completeness, and very nice-looking reports in either Rich Text or HTML format with frames support. Reporting includes your model's data definition information, but unfortunately doesn't include your model diagrams. Layout was not clean, with entities overlapping the relationship line. But everything was spread out enough that with a minor adjustment, the model diagram looked fine.

ER/Studio has two very helpful usability features. With 37 tables, the diagram has to be zoomed out a long way to see all of the tables and relationships-so far that none of the model objects are actually viewable. The Zoom Window shows an actual-size view of what's under the cursor as you move it around your diagram. The second useful feature is a pop-up window that br iefly describes your tables as you move the mouse over them.

Visio Professional With 4Keeps
The diagramming and drawing tools from Visio are some of the best available, and Visio Professional 4.5 is an excellent example of a drawing tool aimed at the diagramming needs of your average IT professional. However, the tool is really only a documentation tool and not an analysis tool. As such, it can't always help you find and correct your mistakes.

A.D. Experts' 4Keeps is an add-in product for Visio Professional that provides both attribute persistence and analysis for data models you create through Visio Professional. The combination of the two tools is very disjointed-you can get the job done, but not easily.

Visio Professional is the tool you use to create your physical data model, and it supports several notations, including IDEF1X, Booch, Chen, OMT, and OOA. In addition, once you install 4Keeps, you can create models using IE, OMT, and several other notations. Visio Pro includes a database rev erse-engineering wizard to map your database into a physical diagram, which is efficiently, but not aesthetically, organized. Unfortunately, reverse engineering a database will not bring it into 4Keeps, which provides your analysis and data dictionary documentation tools.

If you create your data model from scratch using 4Keeps, you'll get the analysis features you will want. 4Keeps Inspector examines your model and reports on its completeness and correctness. 4Keeps Transformer completes the modeling loop by generating a database-either Microsoft Access 95 or an ANSI SQL-92 text file-from your model. Transformer is also responsible for providing the reports you'll want. With Visio, you can save your drawing in a number of graphical formats, including GIF, JPEG, and Windows bitmap. Any of these formats can be easily embedded into a word-processing document.

Using these two tools together seemed harder than it had to be. Better synergy between the products is required before these tools can be recommended f or any serious data modeling.

Andy Feibus is president of CustomBytes, an Atlanta software development consulting firm. He can be reached at amf@mindspring.com .

See a list of Database Documentation Tools as a PDF file.

To view a PDF file,
download the Adobe Acrobat Reader .