Database Diagram (ERD) Design Tool for SQL Server

Database design is one of the key challenges faced by developers and architects. Or, we should say, it becomes a challenge without proper tools at hand.

Cases may vary. For instance, a newly initiated project requires you to create or modify database structures according to the specified requirements. Or you might need to implement certain changes in database structures to enhance security in response to potential threats or compliance requirements. Or maybe it's a matter of regulatory compliance when you need to adapt your databases to the ever-changing industry standards.

Whatever your particular case is, we can suggest a solution to have it all covered. It's called dbForge Studio for SQL Server, and it offers a combination of tools like Database Designer and Table Designer, which help you do the job with maximum convenience and minimum effort.

You can use the Studio to perform the following tasks.

• Visualize database structures using entity relationship diagrams
• Use containers to cluster logically related objects
• Inspect logical relations between tables
• Print out large diagrams
• Create and edit database objects on diagrams
• Reverse-engineer diagrams

An entity relationship diagram (ERD) comprises a graphical representation of database structure and relationships between database objects. In other words, an ERD is used to sketch out the design of a database and illustrate its structure.

ER diagrams vs. ER models

ER model is short for entity relationship model, which is a high-level data model. It consists of a collection of entities (or objects) and their relationships. The terms ER model and ER diagram are sometimes used interchangeably. However, many experts prefer to separate them, emphasizing the conceptual nature of ER Model that describes the structure of data and doesn't explain how exactly it is stored. ER models deal with single entities like a Customer while ER diagrams deal with sets of entities like the Customers table.

How ER diagrams are used

Design databases

When working on a new data model, a database diagram can help capture and document system requirements. It can explain the logical structure and the flow of information within a database in question, as well as assist in enhancing database throughput, debugging errors, and redesigning the architecture.

Troubleshoot and monitor databases

The use of ER diagrams is also beneficial when it comes to locating and resolving issues with logic and deployment. A diagram outlines the structure of a database and makes it easier to understand what and where goes wrong. It helps identify redundancies and bottlenecks within business entities.

Manage business information systems

ER diagrams are used extensively for analyzing and fine-tuning relational databases that are commonly exploited in business processes. Entity relationship diagrams can help understand how those databases can be restructured for data to be retrieved quicker and easier.

This is not an exhaustive list of possible uses. Database diagrams are also used in the reengineering of business processes, education, data analysis, and research.

How to create a new database diagram

Create a new database diagram using SSMS

SQL Server Management Studio—an integrated environment for developing and managing SQL Server databases—allows creating database diagrams in a couple of clicks. To create a database diagram in SSMS:

1. Go to Object Explorer and expand the database node.

2. Right-click the Database Diagram folder and select New Database Diagram.

3. In the Add Table dialog that appears, select the tables to be displayed on the diagram and click Add.

4. Optional: Arrange the tables on the diagram as required.

5. To save the diagram, go to the File menu, select Save Diagram, and provide a name for the new diagram in the Choose Name dialog that appears. Then click OK.

6. Once your diagram is saved, you will be able to find it under the Database Diagram folder.

Create a new database diagram using Database Designer

There is a bunch of decent diagramming tools for SQL Server databases on the market. The one that really stands out is dbForge Studio for SQL Server — an all-embracing IDE for all possible SQL Server-related tasks. The Studio boasts a powerful Database Designer aimed at covering the needs of developers, DBAs, data and business analysts. It includes a number of tools for creating and editing databases, designing tables, building database relationships, etc. With its help, you can design database diagrams and visualize database structures as entity relationship diagrams.

How to create a database diagram in dbForge Studio for SQL Server

In dbForge Studio, you can create a database diagram right from the Start Page. Navigate to the Database Design tab and click New Database Diagram.

Then drag and drop database objects from Database Explorer onto the ER diagram. The database diagram will instantly display the objects and their relationships, thus facilitating further analysis.

To save a diagram:

Right-click the diagram background and select Export to Image from the context menu.

You can save the diagram in the following formats: BMP, JPG, GIF, PNG, TIF, and EMF.

Create and edit database objects on a database diagram

You can visually design databases by creating and modifying database diagrams. Well-designed databases are easy to manage and maintain, they boast improved data consistency and are cost-effective in terms of disk storage space.

Create a new database object on the diagram:

To create a new database object, right-click the database background, select New from the context menu that appears, and then select the type of database object you want to create: Table, View, Procedure, or Function. The corresponding database object editor will open.

With dbForge Studio for SQL Server, you can also visually create a new foreign key relationship between two tables.

To create a new relationship:

1. Click the New Relation button on the Diagram toolbar.

2. Click the child table and move the mouse pointer, holding the mouse button pressed, to the parent table. Then release the mouse button.

3. In the Foreign Key Properties dialog that appears, provide the necessary settings and click Apply Changes.

Modify database objects on the diagram:

To edit a database object, double-click it on the diagram. An object editor dialog will appear. Make the necessary changes in the editor and click Apply Changes to save them.

The View editor looks as follows:

Table Designer:

Table Designer comes as part of the SQL Designer functionality of dbForge Studio for SQL Server. It comprises a visual table editor that allows you to quickly and easily add columns and manage their attributes, create comments and constraints, define indexes, set triggers, work with data, preview changes, and automatically generate SQL scripts based on these changes.

To save your changes, click Apply Changes.

Here is what you can do via Table Designer:

• Create and edit tables
• Edit and manipulate (filter, group, sort, search) table data
• Modify column collations
• Create and/or remove CHECK constraints, define conditions for them
• Add and/or remove unique, primary, and foreign keys
• Permit or restrict values that can be applied to a column
• Add and/or remove unique and non-unique indexes
• Define index types: Clustered, XML, Clustered/Nonclustered Columnstore, or Spatial
• Create and manage statistics
• Create and update triggers
• Define storage properties
• Edit table creation scripts

Tune the design of a database diagram

With dbForge Studio for SQL Server, you can tune the design of the database diagram with a rich set of options. They can be set either globally for all diagrams or individually for a particular diagram.

To set options for the current diagram, right-click it and select Options from the context menu. In the Options dialog, you can configure the settings for the current diagram. You can also reset all the options to default values by clicking Reset.

On the General tab of the Options dialog, you can adjust the diagram appearance: change the background color, select to show the grid, enable shadows, etc. On the Notation tab, you can select the skin. dbForge Studio for SQL Server supports three predefined skins: the Default skin, the Simple skin, and the IDEF1X skin. The Default skin uses the richest color palette. The Simple skin is optimized for printing and does not use gradients. The IDEF1X skin is mostly black-and-white to look like IDEF1X diagrams. On the Page and Print tabs, you can set diagram printing options.

Export SQL relationship diagram

To export a diagram to an image, follow these steps:

1. Right-click the diagram and select Export to Image from the shortcut menu.
2. Specify the target file name and image format.
3. Click the Save button.

How to export large diagrams:

When trying to export a large diagram in dbForge Studio for SQL Server, you might encounter the following error: The error occurred while diagram exporting. Probably the image is oversized. Try export to another format.

Why does this happen? The bitmap-generating engine needs a contiguous memory area for the bitmap. Even if you have enough memory, you might still get this kind of error because of memory fragmentation.

To get around the error:

• Export the diagram to the vector format (emf).
• Use third-party RAM defragmentation and application optimization tools.
• Try to restart the application and export the diagram again.

Database diagram visualization tools

The Studio helps you customize diagram visualization to meet your needs.

Notations. You can choose between two common data modeling notations:

• Information Engineering (IE)
• IDEF1 Extended (IDEF1X)

Relation comments. You can either enable or disable the rendering of foreign key names near the relation child ends.

Document Outline. This window displays the logical structure of your current diagram. You can use it to navigate between tables and their relations.

Other visualization options. You can hide constraints, indexes, and triggers, change their field detail level to display only column names, names with types, or names with full types.

Clustering logically related objects using containers

When you work with a large SQL database diagram, a container allows you to store logically related objects in one place. You can create a new container, add the related tables, rename the container, and then collapse it.

Containers offer a logical packaging mechanism to clarify the structure of complex diagrams and are ideal for those working with large databases.

Tracking logical relations between tables

Virtual relations

• Allow you to visualize logical relations between tables
• Do not exist physically and are only stored on the diagram
• Can be used to simplify the visual analysis of the data
• Can be materialized further into a physical relation

Virtual relation manager contains a list of the existing virtual relations and enables you to:

• Edit the existing virtual relations
• Generate a schema script with the selected virtual relations, edit this script, and execute it to update a database
• Convert a virtual relation into a foreign key

Printing a diagram

Page options allow you to set the following page settings:

• Page orientation
• Paper size and source
• Printing margins and overlapping
• Header and footer text

Display print markup displays the print markup grid. Gray stripes show the page overlapping, which can be adjusted.

Print options allow you to:

• Position a printed image in the center
• Adjust diagram scale
• Set up printing of the page markup that helps to cut and glue together a SQL diagram printed on several sheets

Creating and editing database objects on a diagram

To access the Creating and editing relations option, right-click the line between two entities in the diagram area and perform the following tasks:

• Edit a foreign key using the foreign key editor
• Find a foreign key in Database Explorer
• Synchronize with document outline window
• Reroute a relation
• Show a relation comment

Creating and editing objects. You can create new database objects or edit the existing ones on the diagram area. All the changes you have made to an object on a diagram are permanent and stored on the server.

The Note feature allows you to add comments to the diagram area while designing or analyzing a database structure. You can also add hyperlinks to other resources inside your note. For instance, a hyperlink to wiki pages with database documentation.

The Stamp feature displays the information about a database diagram and serves to identify a printed copy of the document.

The Adding an arbitrary image to a diagram feature allows you to add additional visual information to the diagram area, such as a diagram sketch, or draft.

Searching text on a diagram

Database Designer supports search for database object shapes by text, by their names, and by the names of their elements (columns, constraints, stored routine parameters, etc.). Search can also be performed in the stamp company name and project name fields, notes, relation comments, and container names.

Using additional diagram elements

Notes. Notes are yellow boxes with the text you can add to the diagram to explain diagram parts. Notes can be moved, brought to front or sent to back, or resized like any other shapes on the diagram.

Hyperlinks. The note allows adding a hyperlink to a file if required.

Stamps. Diagram stamp enables you to specify the following information about the diagram: diagram author, company, version, copyrights, project name, and date. You can add as many stamps to your diagram as you want.

Images. dbForge Studio allows adding images to your database relationship diagram. Images can be moved, brought to front, sent to back, or resized like any other shapes on the diagram. You can use the following image formats:

Using reverse engineering to create database models from existing databases

With dbForge Studio for SQL Server, you can easily export database schemas. Invoke Generate Scripts Wizard from the Database menu > Tasks > Generate Scripts. After setting the required connection and database, specify the path and file name for the output, set up a few other options, and click Generate to launch the generation.

You can also create a scripts folder or a snapshot using the Studio. The former is a set of scripts that represent your database schema. The latter is a file that contains information about the structure of your database in XML. Invoke the Create Scripts Folder or Snapshot dialog from the Database menu > Tasks > Create Scripts Folder or Snapshot. Select the required source type (database or snapshot) and specify the connection and the database (or the path to your snapshot). Along with a couple of auxiliary configurations, you set the path to store your scripts folder/snapshot file and click Create to launch the generation.

For your convenience, the generation of scripts folders and snapshots can be automated via the command-line interface using the Save Command Line button.

Using forward engineering to create a database
from a database model

dbForge Studio for SQL Server allows you to create a database from a database model via forward engineering. First of all, you need to create an empty database. For this, navigate to Database > New Database. Enter the database name in the Name field and choose the required owner from the Owner drop-down menu. If required, type a description. Click Apply Changes.

You will see the created database after you click Refresh. To link the database to Source Control, right-click it and navigate to Source Control > Link Database to Source Control.

Then click the plus icon in the Source control repository field and select the folder you have generated with the Create Scripts Folder or Snapshot option in the previous step.

Click OK and select the necessary database development mode. Click Link and you should see the objects for forward engineering in Source Control.

To populate the created database with these objects, just select Remote changes and click Get Latest. All data will be added to the table.

If you are interested in related information, feel free to read the following:

• Generate comprehensive documentation for SQL Server databases
• SQL Server Report Builder