xCase (RESolution, Ltd.) is a desktop data-modeling software package that has developed into a very useful, yet cost-effective, tool for everyday database design and maintenance.

The recently-released “Professional Version 6.01” has much to recommend if you are doing database development in Microsoft SQL Server, Oracle, Sybase, Visual FoxPro, Jet, or many other database platforms.

xCase is a database design tool that helps in the construction and maintenance of database tables, indexes, views, queries and other database objects. With the design interface separated from the actual database, the developer can experiment adding, modifying and refactoring database objects for better efficiency. Designs can be proven and tested before any database objects are generated or modified.

For those of you familiar with SQL Server, consider the diagramming tool included in the Enterprise Manager. If you construct a new diagram with a new table and then try to save it, the new table is automatically added to the database. With a separate database design tool, you can model your proposed changes apart from the live database and then deploy your changes for an application at a later time.

That's just one of the many benefits of database design tools in general. What benefits does xCase provide ?

Powerful and Cost-Effective

xCase offers many of the same features that other expensive database modeling packages offer, at a much lower cost. Database diagrams, the heart of any modeling tool, are easy to create and modify. xCase supports a seemingly unlimited number of diagrams, allowing the developer to break large systems down into manageable pieces. Diagram entities and relations can be color coded, which provide better explanation of relationships, and embedded diagrams can encapsulate groups of tables within a diagram.

In general, I have found the interface easy to use and learn, and the online help file is extensive. A PDF manual for the new version is also available for download. Many aspects of the interface are customizable, allowing you to hide or show various attributes as needed. Once you get your preferences set, however, you shouldn't have to tinker with them much more.

Figure 1: Sample xCase diagram with embedded “Customers” diagram in the top right corner.
Figure 1: Sample xCase diagram with embedded “Customers” diagram in the top right corner.

One of the biggest selling points, for me, are the reverse- and forward-engineering capabilities of xCase. When using ODBC connections, xCase can reach into a target database and import most types of database objects. Once you have the imported the entities and stored procedures into your model, you can make modifications as necessary and then generate a delta script that can be used when you are ready to execute your changes. Since most of my work with xCase has been against SQL Server databases, this has been a very handy feature.

But perhaps the single best feature about xCase is the cost. The “Professional” edition, which can work with most major database platforms, lists for $799, as compared to ERwin, which is four or five times that?not to mention training costs. This low cost and the power of xCase makes it a bargain.

What's new in version 6.01?

When I started to work on this review, I had a number of complaints about xCase to reveal, but many of my issues have been fixed with the new release. For example, relations between tables can now be hidden, which can make complex diagrams look less convoluted. Views can now be added to diagrams (they weren't available before). Forward- and reverse-engineering for SQL databases have been improved, allowing you to synchronize all database objects (previously, only table changes could be moved forward). And, in general, performance is much faster and the the interface has been enhanced.

For this version, the biggest change is the built-in ability to work with Visual FoxPro databases. Previously, reverse- and forward-engineering had to be done with a separate external add-in component. Now this can be done entirely from the xCase interface. There are many other changes, which you can read about on the RESolution Web site. Additional features for remote databases have been planned for a follow-on service pack, which is expected shortly.

What's Not to Like?

Figure 2: The Forward-Engineering dialog box. All DB objects can now be forward-engineered.
Figure 2: The Forward-Engineering dialog box. All DB objects can now be forward-engineered.

As with any software package, there are a few quirks to xCase that you'll need to recognize. User-defined functions, new to SQL Server 2000, still aren't handled quite right. For example, when I tried to synchronize the model to my test database, the forward-engineering wizard said that the UDFs were missing when in fact they were already present. This resulted in errors when running the script. There are also limited export capabilities. For a project I'm currently working on, being able to dump a diagram into a JPG file would be very useful.

xCase also seems to be fairly rigid in determining how relations should work. Unless a parent column is marked as a primary key, the reverse-engineering process ignores a relation between two tables. SQL allows relations to be set if the candidate column in the parent table has a unique index, and the relation is included in an SQL database diagram. This won't happen in an xCase diagram, which can be frustrating if you are reverse-engineering a DBMS with which you are not familiar.

Again, most of the problems I experienced with xCase were fixed in the new version, and the staff at RESolution has been very helpful and responsive with issues I've brought to them.

Got Data? Get xCase!

At the risk of making this sound like a product pitch, xCase is a product that I highly recommend. The power and improvements in version 6.01, coupled with a very affordable price compared to other data modeling tools on the market, make it a great solution for any database development shop. For more information about xCase, check out their Web site at www.xcase.com.