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?
- My job doesn't require it.
- I don't have the proper software.
- I don't know how.
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.
Prev. page
[1]
2
3
next page 
