Brush Up on Design Essentials with the SQL Diagrammer

How long has it been since you did any data modeling? Yesterday? Last month? A hundred years ago when you were in college? If you fall into the last category, what's your excuse?

If you answered B or C, I have good news for you: If you have SQL Server 2005 or 2000, you have a data-modeling tool in the box. Sometimes known as the DaVinci tools or the Database Designer, the SQL Server Diagrammer is listed in the SQL Server 2005 Management Studio tree as "Database Diagrams" and in the SQL Server 2000 Enterprise Manager tree as "Diagrams." This is the SQL Diagrammer, software that will help you describe your existing database schemas and even give you a hand designing future databases. As we walk through how to use the SQL Diagrammer, you'll see how this basic tool can give you a good visual database representation that's easy to work with.

If you're a regular reader of my Solutions by Design column, you know that the importance of good database design is one of my favorite themes. No amount of indexing, clever programming, or beefed-up hardware can compensate for a poor design. But model first, get the design right, and you're on your way to a great-performing database. (For a discussion of the importance and benefits of good database design, see the Web sidebar "Why Model?" at InstantDoc ID 49184.And for an overview of dimensional modeling for business intelligence (BI), see the article "Dimensional Modeling Basics," page 35.)

Modeling software is used 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 the 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 that will mitigate those aches and pains. A variety of software is available for database modeling, and the Web sidebar "Describe and Design" at InstantDoc ID 49185 explains the kinds of features you can get with different kinds of modeling software. SQL Server's modeling tool is fairly basic; it's best use is for describing, so it might be more accurate to call it a diagramming tool rather than a modeling tool. For our purposes in this article, I'll 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.

Before we get started with data modeling in the SQL Diagrammer, I want to make this important point: At all times when you're working with the SQL Diagrammer, you're connected to a live database. The Web sidebar "Describe and Design" explains the dangers and limitations of working with a live database.To avoid serious complications, I suggest you practice against Adventure-Works, pubs, or Northwind on a test or development server before taking your new skills to a production server.

Getting Started with Data Modeling

If you're just getting started with data modeling, or if you've never used modeling software before, I suggest you start with describing—cataloging and inventorying your database environment. This process is analogous to newbie programmers starting with application maintenance; it's a way to "get your feet wet" in the data-modeling environment.

The UI for the SQL Server 2005 Diagrammer is similar to that of the SQL Server 2000 Diagrammer. Although I'll include information about the SQL Server 2005 SQL Diagrammer, I'll concentrate on the more familiar SQL Server 2000 Diagrammer and use the pubs database for this brief explanation of how to get started with data modeling.

Describe a Database

Your first step in describing your database is to reverse-engineer the schema into the SQL Diagrammer. This technique is the easiest way to get familiar with what the data model looks like in the SQL Diagrammer, what artifacts it creates, how they're laid out, and what they're composed of. If you do no more than reverse-engineer each of your databases into a data model then print it out, along with scripting the database, you'll have made a good start toward cataloging your database environment.

To begin, open SQL Server 2000 Enterprise Manager or SQL Server 2005 Management Studio. Expand the navigation tree in the left pane until you find the pubs database (AdventureWorks for you 2005 people). Expand the navigation tree below pubs, and you'll see that the first entry in the tree is Diagrams; this is the SQL Diagrammer. Right-click Diagrams, select New Database Diagram, and follow the Create Database Diagram Wizard to create your first data model.

Figure 1 shows a reverse-engineered model of the pubs database.The objects you see on the canvas (the light-colored background) are artifacts that represent tables, columns, and relationships in the live pubs database. Any changes you make to the architecture, such as adding or deleting a column, creating or removing an index, or even changing the name of a column, will be reflected in the live database as soon as you save the change in the SQL Diagrammer. For example, if you rename the price column in the Titles table to PricePerUnit and save that change, the column name in the live table will become PricePerUnit. If this were a production environment, you could cause all kinds of chaos as the SQL Server acquired schema locks on the table, blocking queries from other users, and as applications that called stored procedures which included the price column began to fail because there was no longer a column called price in the Titles table.

Now that you have the artifacts on the canvas, click the canvas to deselect all the table artifacts. You can rearrange the table and relationship artifacts simply by clicking and dragging. Don't be afraid to rearrange the artifacts; you want a comfortable layout that works for you.

Choose Your Display Type

You have some options regarding what to display within the table artifact. On the top toolbar is a Show icon, which is a drop-down list of the different display presentations available for the model.You can display table names only; name plus keys (primary and foreign); column names (the default); or my favorite, the standard display, which shows column names, data types and lengths, nullability, keys, and of course, table names.You can also create your own customized display.The easiest way to configure the custom properties is to select one or more table artifacts, position the cursor over one of the selected artifacts, right-click, and choose Table View, Modify Custom. When you've got all the column properties you want in your custom display, click OK.The selected artifacts will then automatically switch to your custom display.

You don't have to apply the same display type to all artifacts in your model. By simply selecting a table artifact and choosing a display type, you can adjust the model and the artifact properties it exposes.

Figure 2 shows the pubs model with a standard display, rearranged so that I can see all the artifacts at the same time without having to scroll around the canvas.To change from the default (column names) to standard, select all the tables by clicking the background to deselect any table artifacts, then press CTRL-A to select all, click the Show icon, and choose Standard.

The SQL Diagrammer doesn't automatically adjust the position of the artifacts when you change display types, so you'll probably have to spend a little time rearranging the artifacts so that you can better view the results.You can let the SQL Diagrammer do the auto-arranging by clicking the Arrange Tables icon on the top toolbar. There aren't any hard-and-fast rules regarding how to arrange the artifacts in your data model, but I do have a few rules of thumb to make viewing easier. Arrange the artifacts in the data model so that they're easy to see and easy for you to use. Adjust the relationship artifacts so that the relationships are obvious; try not to let relationship lines cross or overlay one another. And don't cramp the artifacts so closely together that you can't easily discern the cardinality of the relationships.

At some point, you'll want to save your work.The SQL Diagrammer doesn't do the best job of placing artifacts on the canvas, so I find that when I'm doing a lot of work rearranging them, I don't want to have to do it more than once. Before you save, label the model to indicate who created it and when by clicking the New Text Annotation icon on the toolbar. This icon will open a text box in the middle of your canvas. Fill it in (it's free-form), then drag the label to a spot on the model where it'll be visible but won't cover a table artifact.

To save your model, click the Save icon on the toolbar. If all you've done so far is reposition artifacts, you're not affecting the live database at this point.You can rearrange artifacts to your heart's content, choosing first one display and then another, and still not adversely affect the live database. You can zoom in and zoom out, and scroll around the canvas. By using the right mouse button and the right-click menu, you can auto-size one or all of the tables so that all the content of each table is visible. To enlarge and center one table artifact, Zoom To Fit, select the table, right-click, and choose Zoom to Selection.

You can use text labels to add information to your model, but don't make the mistake of using them to provide additional descriptions about your table or relationship artifacts.The SQL Diagrammer doesn't provide a way to attach a label to an artifact. If you want to further describe table artifacts, you can use the Properties box to create a data dictionary.

Manage Table Properties

From the SQL Diagrammer you can manage table properties. Figure 3 shows the Sales table selected and its Properties box open to the Tables tab. The Properties box also has tabs to let you view, create, delete, or modify columns, relationships, indexes or keys, and check constraints. Just remember, though, that you're connected to a database, so any changes you make in the SQL Diagrammer will be made to the database too.

You can create a basic data dictionary by using the extended properties feature to add descriptions for tables and columns.You can do this in a couple of ways.The first way is to fill in the Properties box for the table and column artifacts in the SQL Diagrammer, as Figure 3 shows. For example, you can see that the description for the Sales table says "This table contains all information on book sales."When you add descriptions for tables and columns you're documenting the purpose of each of the artifacts—a best practice that you should try to adhere to as much as possible.The second way to add descriptions for tables and columns is to create a batch query for SQL Server 2005 or 2000 by using the sp_addextendedproperty stored procedure in Web Listing 1, which you can download at InstantDoc ID 49100.

If you need to view the actual data in the database, you can do that from the SQL Diagrammer too. Select a table artifact, right-click, and choose Task > Open Table. This action is the equivalent of using a SELECT * FROM TABLE statement, so use it with discretion on your production systems. Figure 4 shows the data set, which this operation returns in a Microsoft Query interface. If you've had experience using Microsoft Excel as a query tool, you'll recognize the interface immediately.In Figure 4, I've clicked the SQL icon in the top toolbar, which not only lets me see the result set but also gives me the ability to write T-SQL queries against the data. Again, when you're working with the SQL Diagrammer you are connected in real-time to the database.

Define A Database

Once you're comfortable with the SQL Diagrammer, you can start using it to design databases. Unfortunately, the SQL Diagrammer supports only the physical data model; the tool doesn't have the conceptual or logical modeling capability that you'll find in other data-modeling packages. Conceptual modeling is important because the conceptual model is removed from the physical restrictions and idiosyncrasies of the SQL Server platform.This level of abstraction lets you conceptualize business decisions and lets you concentrate on the why and ignore (at least for the moment) the how. If you can live with the limitation of working with only the physical data model, you can use the SQL Diagrammer to design a database. If you're struggling with not having a conceptual modeling tool, then you might want to resort to the old-fashioned method—that is, paper, pencil, and a big eraser.

In the SQL Diagrammer,you start defining by creating a database, using the New Database feature of SQL Server 2000 Enterprise Manager or SQL Server 2005 Management Studio. Unfortunately, without a logical model abstraction layer, you'll have to calculate the space needs by hand; just do your best. Once you've created the database, select it in the Enterprise Manager or Management Studio tree and open it. Select Diagram (SQL Server 2000) or Database Diagram (SQL Server 2005), right-click, and choose New Database Diagram. Because the new database doesn't have any tables yet, you'll get a message that directs you to create the tables from within the SQL Diagrammer.

To create a table in the SQL Diagrammer, click the Add Table icon in the top toolbar. Figure 5 shows the UI that you'll use to graphically build the tables.This figure shows a test database that I created to illustrate this concept. Once you save the table, you can open the Properties box and finish the job. You'll need to assign primary key status to a column in each table, giving it the identity property if you feel it's the right thing to do.You can enter a description of the table and each of the columns in addition to fine-tuning the properties. If you need to define a check constraint on any column, you can do so through the Properties box too.

Repeat this process to create the other tables in your new database.Then, create the relationships between the tables, assigning properties such as enforce for replication, enforce for inserts and updates, and cascade changes or deletes. If you've forgotten to define a primary key or a unique constraint on the parent table in a one-to-many (1:M) relationship, you'll get an error message encouraging you to do so. Continue creating relationships as needed until your data model is done. Now you're ready to go on to the reporting section.

Reporting

Any data-modeling software worth your consideration should have a reporting mechanism. How else can you circulate the work that you've done on the data model? Remember the reasons why you're modeling in the first place: to ensure a good basis for optimum performance, to share a visual representation of the database schema with other people who are invested in the database, and to archive and track changes that are made to the database.

The SQL Diagrammer doesn't have a reporting facility. Instead, you simply use the Generate SQL Script feature of SQL Server 2000 Enterprise Manager or the Generate Script feature of SQL Server 2005 Management Studio and combine the script with a printed copy of the data model.

To create the script, from the Enterprise Manager or Management Studio tree, highlight the database, right-click, choose All Tasks > Generate SQL Script (SQL Server 2000) or Tasks > Generate Scripts and follow the Scripting Wizard (SQL Server 2005). Adopt a naming convention for the output file (e.g., Pubs_script_all_objects_2005dec02.sql) which will readily identify what you scripted and when.

Table and column descriptions are stored in the sysproperties table (SQL Server 2000) and in the sys.extended_properties catalog view (SQL Server 2005).You can generate basic data-dictionary reports by using Web listings 2 and 3, which you can download at InstantDoc ID 49100. Run the code in Web Listing 2 to generate table-level and table-plus-column-level descriptive reports from SQL Server 2000. Run the code in Web Listing 3 to generate the same from SQL Server 2005.

No database report is complete without a picture of the data model. I've heard a lot of people complain that they have no control over how the model is laid out, so that when they print the model, a single table artifact is split between two sheets of paper—very inconvenient. It's true that the algorithm that controls how the SQL Diagrammer lays out the artifacts isn't cognizant of where the page breaks will be, but you can manage the breaks manually. Position the cursor over the background canvas, right-click, and choose View Page Breaks. Now you can adjust the artifacts so that splitting of tables is reduced or eliminated.

Learn Modeling Basics

Most databases are large enough and complex enough that you'll want some software assistance to create the data model. In addition, as the database schema changes over time in response to changing business needs, data-modeling software can keep the database schema and the data model synchronized so that you'll always have a picture of what your database looks like.

Modeling software lets you describe and design. If you think that your database needs to be redesigned to enhance performance or meet business requirements, the first thing you have to do is describe it;catalog what you already have to support your arguments for redesign. Once you get the go-ahead and the budget, you can move into designing databases. Or if you're new to data modeling, start with describing so that you can get familiar with how the data model is constructed.

The SQL Diagrammer doesn't do everything; it isn't the most sophisticated modeling tool on the market. When you're talking about modeling software, price and capability go hand-in-hand. Typically, the more you pay, the more functionality you get, and the SQL Diagrammer lacks a lot of features in high-end modeling software. But it's a way to get basic descriptive modeling done with minimal effort.And it's definitely a step above the paper, pencil, and big eraser routine!