To Case or Not To Case - By Joe Geller

Case tools for database design have been around for a while now. At one time they were the latest silver bullet to solve all of the I/S problems - speed of development, reuse, etc. As with other silver bullets, it was soon found to not be the answer to life, the universe and everything. Not only that, it was often found to be costly, time consuming and of little benefit.

So why am I talking about it? I feel that in most of the situations where case tools were a failure, it was due to the misuse of them, rather than a problem with the tools themselves. If properly used (and I will explain how they should be used), they can provide many benefits, including better database designs, faster development and better communication between the DBA and programming staffs. They will not be a silver bullet, but they can be a valuable addition to your development toolset.

In this note, I will list why case tools have failed, how to use them successfully, and tell you the ones that I like.

Why have case tools failed?

First let's start with some definitions.

• A (database design) case tool is a tool for designing and implementing databases.
• A tool is:
  1. something to make your job easier.
  2. something to make the result better.
  3. see rule 1.
  4. see rule 1.
  5. see rule 1.
     

Many shops decide to do things the "right" way. They develop a detailed methodology for database design (and application design too) and for the use of a database design tool. There are many rules to follow (after all, if you don't follow the methodology, it won't be good, it won't meet standards, and the world might come to an end). What all of these rules accomplish is that the use of the case tool is very cumbersome, it takes more time to use than is saved by the tool, no real work gets accomplished (just lots of documentation), everybody hates the case tool, and finally, nobody uses the case tool.

The things to remember are:

• A case tool and a design methodology are a means to an end.
• The case tool is not the end.
• The methodology is not the end.
• The end (the goal) is a system (both a database and a set of programs) that are "good" (whatever that means) and developed in a timely fashion.

So, What is a Case Tool Good For?

A good case tool can make it easier to design a good system. It can make it faster to design and document a good system, or to document an existing system. It can also help to communicate the design to other DBAs, programmers, and users.
The idea is to keep things simple and let the tool do the work for you. A good case tool can help you document your design faster. It is especially good at speeding up changes to the design. Printing of a database design can also be done quickly with a good tool. This makes it very effective for communication. A nicely laid out database design on 1 or several pages is very easy to read, to talk about and to reference.
Some case tools also help you in the design and verification of a data model or database. They incorporate design rules and DBMS specific rules that can be used to verify your design or to help with transformations. They can also capture existing DDL to produce a design model for you (often with a lot of reformating to make it really usable).

What to look for in a Case tool

Case tools are not all equal. Some really are too cumbersome to be of real use. The best tools have the following characteristics:

• Easy to use - this is the primary consideration
• The ability to hide or show different parts of the design. Full detail can make a cluttered document (or screen) and require more pages. Sometimes you want to see all of the attributes and relationships, sometimes you just want to see the tables or entities.
• Sizing the entities/tables. If you are showing all of the columns, you need to make some tables larger, but you want to keep the ones with fewer columns small.
• Printing flexibility - The best feature of a data modeling tool is the ability to print out the model on (very) few pages. This makes it easy to distribute, use as a communication tool, and to keep handy for your own reference when supporting the database. The best case tools allow you to adjust the size of the output so as to achieve the best balance of readability and number of pages.
• Transforms - this includes capturing existing DDL to use in the database design, transforming a physical design to a logical data model, and going in the other direction (logical to physical and physical to generated DDL).

There are many other features to look for, but I feel that these are the essential ones for making it a usable tool.

Which Ones do I Like?

There are many data modeling tools available. I have not used them all. Of those I have used, I think that some are worthless and others very good. I will just give you the 2 that I like the best.

• Bachman (now Cayenne Software) - The original versions of this (Bachman Analyst and Bachman DBA (for DB2) run under OS/2. They are costly. While not perfect, their ease of use and flexibility of printing make them a very good set of tools. Bachman now has versions that run under Windows. The Analyst is now called Groundworks and the DBA is called Terrain for DB2. There are also Terrain versions for SQLServer and ORACLE. Bachman recently merged with CADRE to form Cayenne Software.
  
• S-Designor This tool is now owned by Powersoft. It is both a logical data modeler and a physical design tool. There is now also a physical database only tool called Star-designor that is much less expensive.

Summary

Developing a system properly includes designing and developing a good data model and database. A good case tool can help you make a better design and help you communicate this design and its implementation to the other people involved in the system. The key to successful use is to keep things simple and let the case tool (and the data model) be a tool to help you, not an end in themselves.

Database Design Tips

Back to DB2 Main Page.
Designing Start & Stop Dates.
DB2 Version 4 Top Ten List.
Avoiding Lock Contention in a Client/Server Environment.
Data Compression - I/Os Go Down, CPU Time Goes Down??.
Checking For Existence.