Why Modeling Tools are not Enough

If the data model has flaws, the database schema will be flawed and the information produced will have poor quality.

Debugging the Data Models

In the majority of companies and government entities, data models are created using modeling tools. These tools, such as CA’s ERwin®, Embarcadero®’s ER/Studio, Oracle’s Designer, and Sybase’s PowerDesigner are invaluable. They help the database designer – data modelers, data architects and DBAs, create the data models in much less time than doing it manually. In addition, the graphical representation of the data models is very convenient to help the end users understand the flow of the data.

All these modeling tools have some validation for completeness of the model, but they do not have the intelligence to “debug” the model for integrity, consistency and for compliance with the rules of relational technology. Therefore, a data model created with a modeling tool may and probably will have flaws, quite possibly major ones.

The Importance of the Database Schema

According to Joe Celko (www.celko.com), a relational database guru and published author, a flawed schema adversely affects:

  • The quality of the information produced and
  • The performance of the application

If the database schema has flaws that go undetected, the information produced by the application will be poor, and decisions based on poor information will have major financial consequences. You do not have an option; schemas must be flawless.

To avoid unnecessarily high costs of development and potential financial risk, it is imperative to “debug” the schema for consistency, integrity and compliance to the rules of relational technology.

Early Detection of Flaws Saves Time & Money

It is imperative that flaws in the schema are detected and fixed as early as possible. Ideally they must be fixed at the data model level, or at the DDL level (if then model was created manually), or at the database schema level, when the schema was initially created (no data in the database and no programs being written). If the flaws are detected in any one of these situations, the time and cost for fixing the issues is minimized.

In the event we must fix the issues after the database is populated and there are programs accessing the database, the time and costs will increase substantially. In some situations, for the schema to be corrected, data may have to be downloaded, the schema then updated, data uploaded and the affected programs changed and re-tested. This is the major cause of extremely high development costs and late delivery of the applications.

development without database examiner

In the picture above, you can see that if a schema flaw is detected, it can be corrected ideally at the modeling tool level, or at the SQL/DDL scripts level or at the schema level.

DBE Software’s Database Examiner Debugs the Schema

Examiner was created with the specific mission to help debug the database schema in every stage, as a data model, as a set of SQL/DDL scripts or as the schema itself.

Most companies and government agencies use modeling tools to create the data models. In this case it is better to “debug” the data model instead of waiting for the schema to be generated; this is where the greatest savings will be made. Examiner can directly read data models from ERwin or from ER/Studio. Every modeling tool generates DDL scripts to the DBMS to create the database schema. Examiner will read either the DDL scripts or the database schema directly. If the schema is generated manually (no modeling tool), Examiner will still be able to help by directly validating the DDL scripts or the schema.

development with database examiner

You can see by this picture that there is a direct relationship between the data model, the schema and the information. A flawed data model means a flawed schema which in turn means flawed information.

Maintenance with Examiner

When changes are proposed for a production schema, Examiner can run a “What If” simulation of the modifications to understand the ramifications. Examiner can read a production schema and virtually “merge” it with a set of DDL scripts containing the proposed changes. If the proposed changes negatively affect the schema, these changes are reviewed and modified until they are sound. Then the changes are implemented to the schema. By simulating the changes, costs will be kept to a minimum in all maintenance projects.

maintenance with examiner

Summary

The facts:

  • Modeling tools validate the model for completeness, not correctness
  • Modeling tools do not “debug” the data model
  • Data models are the basis for the database schemas
  • A flawed schema imparts poor information
  • Schemas must be flawless
  • Examiner “debugs” the schema
  • Flawless Schemas produce quality information
  • Decisions based on quality information cost much less
  • Examiner is a powerful complement to any modeling tool
Prepared by Luiz C. Siqueira
February 2008
Download PDF