Creating an ERD (in Visio)

Introduction

While the process of performing an Entity Analysis can be informal, many project choose to formalize it as part of the Defining stage of SDLC. Formalizing the process usually involves creating and documenting Entity Relationship Diagrams (ERD's) to be used as the basis of designing the database structure. The ERD is a conceptual drawing of how a database is structured. An ERD is NOT the actual database design, and does not describe all the details and rules about the database. But it is a ueful tool to guide the database design, and provides a visual diagram of the database structure.

One common tool for creating ERD's is Microsoft's Visio software. Visio is a tool to create charts, diagrams and other visual drawings. There are also free online tools available (Source: Top 5 Free ER Diagram Tools), such as

Visio Basics

If you are familiar with Visio, skip to the next section on Creating an ERD Diagram.

This section is intended to provide only the bare basics of creating and editing a Visio document.

When starting Visio, the program presents a left panel with previously created documents and a right panel listing templates for creating a new document. A new document is started by double-clicking a template, such as Basic Flowchart, or by right-clicking on the template and selecting Create. Visio then opens a dialog Window allowing you to choose either Metric or US Units, and to start with a blank document or a pre-defined color scheme.

Assuming you select a blank document, Visio presents an editing view similar to the one below.

Figure 1:

New Visio Empty Document

Figure 2: New Visio Empty Document

Figure 3:

Figure 4:

Figure 5:

Figure 6:

Figure 7:

To save the Visio document, use the ribbon File/Save As option. Visio uses a ".vsdx" file extension.

Creating an ERD Diagram

Although several templates can be used to create an ERD diagram, Visio also contains templates specifically for the purpose, such as the Crow's Foot Database Notation template.

Figure 1:

The Crow's Foot template only contains 5 shapes, of which the Entity shape is normally the only one you need to start with. The easist approach is to drag a new Entity shape to the document, stylize it while it is still selected (change the font-size of the Entity Name to 18pt or higher. If needed, it is always possible to click and select just the Entity Name.

Continue stylizing the first entity by selecting all the attributes (draw a cursor box completely around them). Stylize the attributes (font-size of 14pt or higher).

Select the entire entity box and copy-paste it (CTRL-C, CTRL-V) to add additional entities. This can also be done after editing the entity name and attribute names.

Figure 2:

Enter/edit the Entity Name. Remember, that the entity name should be singluar; it represents a single instance of the data.

Enter/edit each attribute. To add a additional attribute, click on any attribute and copy-paste.

Attributes can be re-ordered by dragging with the mouse. As with all other Microsoft products, keyboard shortcuts such as CTRL-Z (Undo) and CTRL-Y (Redo) also work in Visio.

To designate an attribute as a foreign key (FK), right click to display a context menu that includes a "Set Foreign Key" option.

Figure 3:

Each lines in an ERD should be annotated to describe the relationship. This assists the database designers in making sure the table structures and keys support the correct relationships. In almost all cases, the correct relationship is for an FK entity to "point" to a PK entity. If the line description uses a passive voice, such as Account "is owned" by Customer, the description will match the line direction from the FK entity to the PK entity.

Figure 4:

Connector lines in a Crow's Foot diagram are specialized entity relationship arrows. Each line describes how the entities are related. In general, the first step is to draw the arrow "from" the table with the FK and "to" the table with the PK. In most cases, the attributes have the same name.

The default arrow displays an end-arrow icon that represents zero-or-more instances of the FK entity, and 1-and-only-1 instance of the PK entity. For example, this means that multiple Account instances may be owned by a single Customer. It also means that a Customer may exist with any Account.

Figure 5: