Describe and Design

Data-modeling software is usually packaged in one of two ways: as a standalone package (e.g., Computer Associates ERWin, Telelogic (formerly Popkin) System Architect, Oracle Designer) or bundled into a toolkit (e.g., Microsoft Visio, Sybase PowerDesigner). You can pay a little or a lot for the software, and the feature set is more or less commensurate with the price—in general, you get what you pay for. Usually, the more affordable the software, the more limited the features.

Likewise, you use modeling software in two ways: to describe and to design. When you're describing, you're using the modeling software to catalog what you already have in-house: the systems, processes, data stores, applications, and software systems that make your company great and the documents that describe how you do what you do. When you're designing, you're looking into the future. You're listening to the aches and pains of users from your front-line workers to your C-level managers (e.g., CEOs, CFOs) and you're using the modeling software to design data structures to mitigate those aches and pains.

In high-end modeling software, you can integrate different types of models, linking requirements models, data and process flows, and various UML and XML diagrams to conceptual, logical, and physical data models. This ability is handy if you're in an industry in which you need to prove compliance traceability. By using the modeling software, you can integrate different database platforms and servers. So, for example, you can get a handle on how the tables in your Oracle database compare to those in your SQL Server database. And by using the modeling software, you can generate free-standing DDL and DML (i.e., user-defined functions, triggers, and stored procedures), which you can use to document existing databases or to create new databases. You can maintain active links to the live databases and with the click of a mouse see the production database schemas, then generate hard-copy and Web reports describing your database environment.

The high-end software supports teamwork; you can divide a large data model into segments and assign different people to work on each segment. Typically, such software provides a check-out, check-in scheme that prevents unintentional overwrites and loss of data in the model. For this feature to work, the modeling software has to connect to a repository—a database where the data models reside. This level of modeling software can also contain data-transition functionality (moving data from one platform to another, regardless of whether the platform is a “database platform”), change-control functions, and “what-if” reasoning with impact analysis.

SQL Server's modeling tool, the SQL Diagrammer, is fairly basic; it's best for describing, so it might be more accurate to call it a diagramming tool rather than a modeling tool. In the main article, I refer to the SQL Diagrammer as a modeling tool, and the graphics it creates as models. Once you're comfortable with how the SQL Diagrammer works, you can use it to design databases.

Caution: Live Database

Before we begin reviewing how to get started with data modeling and the SQL Diagrammer, I want to make this very important point: At all times when you're working with the SQL Diagrammer, you're connected to a live database. Therefore, I suggest that you practice against pubs or Northwind or AdventureWorks on a test or development server before taking your new skills to a production server.

Linking persistently to a live database when you're modeling can be problematic. In my mind, the biggest problem is that you can inadvertently alter or lose data or adversely affect production. If you remove a column from a table artifact in the SQL Diagrammer, you're removing live data from the database. Once you've saved the change in the SQL Diagrammer, the data is gone. The SQL Diagrammer works this way: When you make a change to a table in the SQL Diagrammer, the Diagrammer issues the following instructions to the SQL Server:

This scheme in itself might cause you to rethink whether you want to use the SQL Diagrammer for your larger production tables.

A second limitation to the persistent live link is that you have to have Enterprise Manager or Management Studio open on your desktop to use the SQL Diagrammer. There's no disconnect, no checkout, no taking the model offline to work in a “safe” environment, no check-in mechanism to return the model to the repository. This requirement is OK for describing because you're essentially just cataloging what's already in place. But for designing, it's not such a good idea. Designing is definitely a creative activity that requires uninterrupted quiet—a resource that's not abundant in your typical computer room or IT office.

Requirements and Limitations

The SQL Diagrammer is limited to only those server types that can be registered in Enterprise Manager or Management Studio, but it's a giant step toward helping you get a graphic representation of your databases. If you're in a mixed environment—that is, you've got both SQL Server 2005 and SQL Server 2000 servers in your shop—the SQL Server 2005 Diagrammer can't help you manage SQL Server 2000 data models. Even though the two data models might look alike, according to SQL Server 2005 Books Online (BOL), “SQL Server 2005 database diagrams and SQL Server 2000 database diagrams are created and rendered differently”. Microsoft recommends using Enterprise Manager to manage SQL Server 2000 data models and Management Studio to manage SQL Server 2005 data models. If you upgrade a server from SQL Server 2000 to SQL Server 2005, any data models will also be upgraded. The first time you open the Database Diagrams folder for a newly-upgraded database, SQL Server 2005 will convert the models. So, if you're in a mixed environment, don't get rid of all your Enterprise Manager installations. Make sure you've got a desktop computer with the SQL Server 2000 tools installed, especially Enterprise Manager, so you can describe and design in your SQL Server 2000 environment.

The SQL Diagrammer can't integrate data models across databases and it can't merge or combine database schemas; it's limited to one database at a time—the database that it's associated with. You can't render a SQL Server database diagram into segments for team projects; it's just not that sophisticated a piece of software. And I know of no way in the SQL Diagrammer to provide compliance traceability—that is, associating requirements from a compliance document to structures or objects that will prove compliance enforcement. So, if your company collects Payment Card Industry (PCI) data, how will you prove compliance? If the only modeling tool you have is the SQL Diagrammer, you'll have to do it manually.