Database Design Tools

Designing a database that's flexible, fast, and efficient isn't easy. In broad terms, it involves learning about and analyzing an organization's information needs, conceptualizing what the database will look like and what data it should store to meet those needs, transforming that conceptual representation to a logical design that includes rules and information about the structure and type of data, and adapting the logical design to a database management system (DBMS).

SQL Server comes with SQL Diagrammer, a basic tool that some people use to design databases. But SQL Diagrammer is a bare-bones tool with some significant limitations. (For information about some of those limitations, see the Web-exclusive article "Describe and Design.") A third-party database design tool is a better choice. This buyer's guide brings together nine such tools so you can compare them side by side.

A Picture Worth a Thousand Words

To build the efficient, high-performance database your organization requires, you need to use data modeling - the art of creating visual representations of a database's data, relationships, and other elements. "No amount of indexing, clever programming, or beefed-up hardware can compensate for a poor design. Model first, get the database design right, and you're on your way to a database that performs well," points out Michelle A. Poolet in the Web-exclusive article "Why Model?" (February 2006). She notes that a model, being a visual representation rather than Data Definition Language (DDL) code, also makes it easier to understand what kind of data your database contains.

There are three types of data models:

As you can see, the three models step you through the design process, so database design tools that support all three models are preferable. Such tools provide the methodology and instruments you need to define your organization's data requirements, create a database design that meets those requirements, and implement that design in your DBMS.

You build the conceptual, logical, and physical data models using a data-modeling notation - a standardized set of symbols and formats used to visually represent a database. Common notations are entity relationship (ER) modeling, enhanced ER modeling, Object Role Modeling (ORM), and Unified Modeling Language (UML) modeling. Some notations (e.g., ER modeling) support all three models, whereas others (e.g., UML) support only the logical and physical data models. Because you follow the notation imposed by the database design tool you're using, if you're familiar with a particular notation you'll want to make sure that the tool you choose supports it. Keep in mind that the more notations a tool has, the more options you'll have when designing your database.

Other nice-to-have visual aids include data-flow diagrams (DFDs) and data structure diagrams. DFDs illustrate the flow of data through a system and all the work or processing that's performed on that data as it moves through the system. Data structure diagrams illustrate the relationships within an entity and the constraints between relationships. (If you're unfamiliar with the term entity, see the Web exclusive sidebar "Basic Data Modeling Terms," for a definition.) Data structure diagrams can be provided as a standalone visual aid or as part of enhanced ER modeling. Enhanced ER modeling extends ER modeling concepts in various ways. For example, one extension incorporates data structure diagrams, whereas another incorporates supertype and subtype information.After you determine which database design tools support the data models and notations you want, you can start looking at those tools' features and capabilities.

Automation Is Key

Automation, perhaps the most important feature of a database design tool, lets you go from the conceptual to the logical to the physical data model without having to reenter everything. Tools need automation capabilities to generate the code that will create the database - a process called forward engineering. (I discuss forwarding engineering later.) Some design tools also include capabilities such as automatic generation of indexes and triggers.

Inheritance and validation are subsets of automation. In inheritance hierarchies, a domain or column automatically acquires the metadata of the parent domain or column, which helps ensure valid and consistently formatted data. Inheritance also lets you reuse items, thereby saving time and hassle. Some database design tools support more advanced inheritance capabilities than others - user-defined inheritance and inheritance override, for example. Database design tools use validation to check for modeling errors and inconsistencies (e.g., duplicate names, missing items, incorrect notations) when you go from one data model to the next. Some tools provide more advanced validation capabilities, such as comparing and synchronizing domain properties between two data models and checking data in a database against business rules and defined constraints.

Engineering and Updating Capabilities

When designing a database, it helps to know what your existing database looks like. Reverse engineering lets you create a data model from a database so that you can document its structure in an effort to improve it or to avoid making the same mistakes in the new database. You can also use reverse engineering to compare two databases - either different databases or different versions of the same database - by creating a model for each and then comparing the models. Reverse engineering is a must-have feature, and every database design tool that I'm aware of includes it.

Reverse engineering's counterpart is forward engineering. Instead of creating a data model from a database, forward engineering creates a database from a physical data model. Database design tools that offer forward engineering automatically generate the scripts for the database by using the information in the physical data model. Because this functionality is a form of automation, most database designers consider forward engineering a must-have feature. Although almost all database design tools offer forward engineering, there can be differences in the capabilities that are included. For example, some options might let you create only certain parts of a model (e.g., certain tables) or might only generate a log showing the results.

Besides generating scripts that create the database, many tools generate scripts for other purposes, such as loading the database with data and updating databases. Some tools let you generate database objects, stored-procedure code, and trigger code.

Another must-have feature for most designers is the ability to update a database by making design changes to its data models. Changing a database's design through its data models provides an easy way to accommodate a company's evolving data needs. More important, it helps preserve the integrity of the database because you're not patching it - instead, you're updating it using the same methodologies and instruments you used to design it. You can also make sure the changes don't negatively affect performance.

Nice Features to Have

As I've said, DFDs and data structure diagrams are nice but not essential features to have in a database design tool. A few other features also fall into the nice-to-have category, such as the ability to undo and redo changes when building the model, and version control, which lets you save different versions of a model for archiving, comparison, and modification purposes.

Advanced reporting capabilities are also nice-to-haves. Most database design tools let you produce reports in several formats (e.g., .html, .rtf) and export diagrams as image files. But some tools also offer capabilities such as customizable report templates and an add-on for creating Web sites that display data models.

The Bottom Line

Database design tools can include numerous features and capabilities - too many to discuss all the possibilities here. If you're in the market for a database design tool, it's worthwhile to identify particular special features you might want. For example, if you're a member of a database design team, you'll probably want a tool that has a central repository for storing designs.

All these tools provide more capabilities than SQL Diagrammer does and go a long way toward making you more productive and helping you build the fast, flexible databases you need. Generally speaking, the more features and capabilities a tool has, the more it costs. If your budget allows you only one bell or whistle, opt for automation. Nine times out of 10, other features and capabilities will be included as part of the automation package.