Masterful data modeling

ER/Studio 7.1 conquers drudgery with easy, insightful tools

Despite what some people may think, data modeling is still the preferred method for creating database schemas. Unfortunately, modeling often gets pushed to the back burner in lieu of creating tables and relationships in the middle of coding the application. For those who would push data modeling to the front burner if only they had a better tool, there’s Embarcadero’s smooth and easy ER/Studio. Version 7.1 adds a number of exciting new features.

You can run ER/Studio either as a stand-alone client or connected to a central repository. The repository allows you to create a centralized database to house your models and apply security to control who can alter models and define enterprise-level data dictionaries for use in all models. The repository doesn’t yet allow access to sub-models without also allowing access to the main model, and it’s hampered by other minor shortcomings as well. Embarcadero, however, says it is working to enhance the functionality soon.

Easy imports

Reverse engineering an existing model is not only easy, it also comes with some attractive options. Usually when you import an existing model you have to spend a lot of time arranging the tables in some kind of meaningful format before you can even begin working; on really large models I’ve seen this process take as long as three days. Using ER/Studio you can reformat your models with a single click into one of seven model designs -- including circular, hierarchical, orthogonal, symmetric, and tree -- and you have plenty of options to control how each layout behaves. You can define handling options for both connected and disconnected entities including, but not limited to, node and edge spacing, staggering, and space between nodes.

You can also easily organize subject areas by defining any number of sub-models or using colored shapes to highlight different subject areas. For example, you could place all of the customer tables in a yellow circle, the distribution tables in a green box, and the billing tables in an orange pentagon. The colors work well for smaller models, but for larger models where you have hundreds of entities to fit on the screen, you will need to define sub-models for each logical section. Sub-models provide a very efficient way to view only those entities you want to see.

Capacity planning

ER/Studio boasts a new capacity planning feature that will help architects project disk requirements on the production system. For each table you can count the number of rows that are currently held. In the capacity-planning screen you can define many options for sizing your physical model including initial size, maximum size, growth rate, index usage, row overheads, and much more. After you fill in the required data, you can generate a report showing projected database growth over time. This is an extremely useful function that often goes overlooked in even the biggest enterprise development scenarios. One little extra I’d like to see added would be the capability to specify expansion percentages. Say a child table is always 25 percent larger than its parent. It would be nice to simply specify the growth of the parent table, and have its children taken care of automatically without needing to do the math.

Lineage basics

The new data lineage feature makes a good start, but it’s not terribly useful just yet. You can define a number of data movement rules for each column in your tables that allow you to document exact standards or expected guidelines for the data in the production system. You can define rules for operations, including backup, updates, archives, recovery, and more. You can also define data sources and targets and map them directly to your model, allowing you to document the dataflow in your environment with relative ease. This is all good. The problem is, getting the information back out of the model in any usable format isn’t nearly as easy as putting it in. You need to write your own code to extract it. It would be nice if ER/Studio could show you the lineage in a nice diagram so you could package it with your data dictionary or publish it by itself.

ER/Studio may be the best overall data modeler on the market. Its combination of features and ease of use puts it leagues ahead of competitors such as Computer Associates’ ERwin. The new capacity-planning capabilities will help architects answer tough questions such as how much space your database will consume one year from now. The repository gives larger shops the capability to wrap security around enterprise data models and define some top-level policies. The data lineage tool still needs some work, but it’s an excellent start and will be of good use to serious data architects.

InfoWorld Scorecard
Features (30.0%)
Administration (15.0%)
Setup (10.0%)
Ease of use (20.0%)
Value (10.0%)
Performance (15.0%)
Overall Score (100%)
ER/Studio 7.1 8.0 8.0 9.0 9.0 8.0 8.0 8.3