Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2005 Data Modeling Tools


By:   |   Read Comments (3)   |   Related Tips: 1 | 2 | 3 | 4 | More > Database Design

Problem
Rarely do we (Developers and DBAs) have sufficient time for our projects and every day tasks.  So when it comes to building a new system, inevitability what happens is time is limited and individual tables end up being built instead of a comprehensive data model.  As the system grows, we hope that the front end code is correct and keeps the data related, but when you have a data problem it may take hours to check and validate that the data is correct.  So how can we break this cycle and migrate from building table by table to a comprehensive data model?

Solution
Working with your team and letting them know the time needed is the first step.  The second step is exploring data modeling tools to streamline the process to be able to work on projects and daily tasks.  With SQL Server 2005, Management Studio includes the Visual Data Modeling tools.  This tool set enables you to graphically build your data model and translates the model into the appropriate T-SQL commands. 

What are the capabilities of SQL Server 2005's Visual Data Modeling tools?

The chart below outlines the primary activities that can be performed in SQL Server 2005's Visual Data Modeling tools:

ID Task Directions Screen Shot
1 Create a new data model Management Studio | root | Databases | database name | Database Diagrams | Right click on the folder | New Database Diagram | Yes (if prompted)

 

2 Portions of the Visual Data Modeling interface When a new data model is created the a blank slate is available.  Four options are available to interact with the tool:
  • Database diagram toolbar
  • Database Diagram menu
  • Right click on the work space or on an object
  • Use the Properties window (View | Properties Window) to get information relative to the active object
3 Add existing tables to the data model Right click on the main work space and select the 'Add Table...' option

Highlight the tables that need to be added to the model and then press the 'Add' button

 

4 Create new tables Right click on the main work space and select the 'New Table...' option

Enter the column names, data types and null configurations

 

5 Add Primary Key Right click on the column that you would like to make the primary key and select the 'Set Primary Key' option

 

6 Building Referential Integrity To build referential integrity, drag and drop the primary key from the parent table to the foreign key on the child table

Then set the properties for the referential integrity

To display the referential integrity labels, click on the 'Show Relationship Labels' on the database diagram tool bar

7 Index Management Right click on the table and select the 'Indexes\Keys...' option

To create an index click on the 'Add' button and provide a name then configure the index properties

To drop an index select the index from the list on the left and press the 'Delete' button

 

8 Constraint Management Right click on the table and select the 'Check Constraints...' option

To create an constraint click on the 'Add' button and provide a name then configure the constraint  properties

To drop a constraint select the constraint from the list on the left and press the 'Delete' button

 

 

9 Saving a change script To save a T-SQL script of all changes since the last time the model was saved, click the 'Generate Change Script' from the Database Diagram tool bar

Click the 'Yes' button to save the contents to a text file in the file system

 

10 Add notes to the data model As a best practice, annotations should be added to the model to help document the database design

This can be accomplished by right clicking on the workspace and selecting 'New Text Annotation' option and entering the comments

 

11 Finalized Data Model Once the entire model is completed all of the tables, columns, primary keys, foreign keys, data types, etc should be configured

 

12 Sharing the data model To share the data model, it is possible to print the model or share it online in Management Studio

To print the model select File | Print

To show the page breaks, select the 'View Page Breaks' icon from the database diagram tool bar

 

 

What are the benefits of using the SQL Server 2005 Management Studio tool?

  • Be able to use a more intuitive point and click interface to build your data models
  • Have the ability to issue a save script for change management purposes and to review the T-SQL commands
  • Ability to share the data model with other users electronically or by printing a physical copy

Caution - Word to the wise...this is a live data modeling tool

  • This data modeling tool is designed to make changes to the database when the model is saved, which means this tool is primarily intended for development environments
  • You are making changes to the database, this is not a mock up tool
  • There is no undo button, once you save CTRL + Z will not bring back your table and data
  • If you drop a table in this model, you will drop it from your database, so be sure when you drop tables
  • Tables are dropped and temp tables are created with the needed columns and the old tables are dropped
  • Indexes are created on the fly which can have a performance impact on the system

Next Steps

  • If you have had a hard time writing queries or working with a user because you could not "see the data model" then reverse engineer one of your databases and share the information with your team.  See if this helps the coding and communication process
  • If you are starting on a new project, rather than building individual tables, use the Visual Data Modeling tools with SQL Server 2005 Management Studio.  See if using this tool streamlines the process and gives you a better understanding of the system from the start.
  • Once you start using the tool, be sure to make your changes in the development environment, save the change script via the tool and then save the data model to retain all of the work in the development environment.  Next migrate the script from development to test and from test to production.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, January 21, 2015 - 4:04:06 PM - Supriya Back To Top

Great post!!

It really helped me in getting the basic idea of data modelling.


Saturday, October 17, 2009 - 3:24:39 PM - admin Back To Top

Logos123,

Do you mean 1 to many or 1 to 1 relationship?

If so, I am not aware of that functionality in SSMS.  I know other modeling tools do support those sorts of notations.

Thank you,
The MSSQLTips Team


Wednesday, October 07, 2009 - 5:32:29 AM - logos123 Back To Top

Can we display relationship properties (not the name) on the Database Diagrams in SQL Server ?

 


Learn more about SQL Server tools