Executive Summary:
|
A database design is a prerequisite for an efficient, high-performance database, but producing a design is a complex, time-consuming job and Microsoft SQL Server's built-in SQL Diagrammer doesn't do a lot to make it easier. The right database design tool will more than pay for itself by increasing your productivity and the efficiency of the resulting database. This buyer's guide puts nine third-party tools side by side for easy comparison.
|
Click here to see the Buyer's Guide table
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,"
February 2006, InstantDoc
ID 49185.) 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, InstantDoc
ID 49184). 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:
- A conceptual data model describes in nontechnical
terms what's important to an organization,
what data the organization collects about those
important elements, and the relationships between
the elements.
- A logical data model turns the information in the
conceptual data model into a technical database
design that illustrates how to build the database.
- A physical data model specifies how to implement
a logical data model in a specific DBMS, such as
SQL Server.
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 Webexclusive
sidebar "Basic Data Modeling Terms,"
InstantDoc ID 96844, 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.
Continued on page 2.
Prev. page
[1]
2
next page 
