Database is absolutely an integral part of software systems. To fully utilize ER Diagram in database engineering guarantees you to produce high-quality database design to use in database creation, management, and maintenance. An ER model also provides a means for communication.

Today we're going to walk you through everything you need to know about ER Diagramming. By reading this ERD guide, you will get the essential knowledge and skills about ER Diagrams and database design. You will learn things like what is ERD, why ERD, ERD notations, how to draw ERD, etc. along with a bunch of ERD examples.

What is an ER diagram (ERD)?

First of all, what is an Entity Relationship Diagram?

Entity Relationship Diagram, also known as ERD, ER Diagram or ER model, is a type of structural diagram for use in database design. An ERD contains different symbols and connectors that visualize two important information: The major entities within the system scope, and the inter-relationships among these entities.

And that's why it's called "Entity" "Relationship" diagram (ERD)!

When we talk about entities in ERD, very often we are referring to business objects such as people/roles (e.g. Student), tangible business objects (e.g. Product), intangible business objects (e.g. Log), etc. "Relationship" is about how these entities relate to each other within the system.

In a typical ER design, you can find symbols such as rounded rectangles and connectors (with different styles of their ends) that depict the entities, their attributes, and inter-relationships.

When to draw ER Diagrams?

So, when do we draw ERDs? While ER models are mostly developed for designing relational databases in terms of concept visualization and in terms of physical database design, there are still other situations when ER diagrams can help. Here are some typical use cases.

• Database design - Depending on the scale of change, it can be risky to alter a database structure directly in a DBMS. To avoid ruining the data in a production database, it is important to plan out the changes carefully. ERD is a tool that helps. By drawing ER diagrams to visualize database design ideas, you have a chance to identify the mistakes and design flaws, and to make corrections before executing the changes in the database.
• Database debugging - To debug database issues can be challenging, especially when the database contains many tables, which require writing complex SQL in getting the information you need. By visualizing a database schema with an ERD, you have a full picture of the entire database schema. You can easily locate entities, view their attributes and identify the relationships they have with others. All these allow you to analyze an existing database and to reveal database problems easier.
• Database creation and patching - Visual Paradigm, an ERD tool, supports a database generation tool that can automate the database creation and patching process by means of ER diagrams. So, with this ER Diagram tool, your ER design is no longer just a static diagram but a mirror that reflects truly the physical database structure.
• Aid in requirements gathering - Determine the requirements of an information system by drawing a conceptual ERD that depicts the high-level business objects of the system. Such an initial model can also be evolved into a physical database model that aids the creation of a relational database, or aids in the creation of process maps and data flow modes.

ERD notations guide

An ER Diagram contains entities, attributes, and relationships. In this section, we will go through the ERD symbols in detail.

Entity

An ERD entity is a definable thing or concept within a system, such as a person/role (e.g. Student), object (e.g. Invoice), concept (e.g. Profile) or event (e.g. Transaction) (note: In ERD, the term "entity" is often used instead of "table", but they are the same). When determining entities, think of them as nouns. In ER models, an entity is shown as a rounded rectangle, with its name on top and its attributes listed in the body of the entity shape. The ERD example below shows an example of an ER entity.

Entity Attributes

Also known as a column, an attribute is a property or characteristic of the entity that holds it.

An attribute has a name that describes the property and a type that describes the kind of attribute it is, such as varchar for a string, and int for integer. When an ERD is drawn for physical database development, it is important to ensure the use of types that are supported by the target RDBMS.

The ER diagram example below shows an entity with some attributes in it.

Primary Key

Also known as PK, a primary key is a special kind of entity attribute that uniquely defines a record in a database table. In other words, there must not be two (or more) records that share the same value for the primary key attribute. The ERD example below shows an entity 'Product' with a primary key attribute 'ID', and a preview of table records in the database. The third record is invalid because the value of ID 'PDT-0002' is already used by another record.

Foreign Key

Also known as FK, a foreign key is a reference to a primary key in a table. It is used to identify the relationships between entities. Note that foreign keys need not be unique. Multiple records can share the same values. The ER Diagram example below shows an entity with some columns, among which a foreign key is used in referencing another entity.

Relationship

A relationship between two entities signifies that the two entities are associated with each other somehow. For example, a student might enroll in a course. The entity Student is therefore related to Course, and a relationship is presented as a connector connecting between them.

Cardinality

Cardinality defines the possible number of occurrences in one entity which is associated with the number of occurrences in another. For example, ONE team has MANY players. When present in an ERD, the entity Team and Player are inter-connected with a one-to-many relationship.

In an ER diagram, cardinality is represented as a crow's foot at the connector's ends. The three common cardinal relationships are one-to-one, one-to-many, and many-to-many.

One-to-One cardinality example

A one-to-one relationship is mostly used to split an entity in two to provide information concisely and make it more understandable. The figure below shows an example of a one-to-one relationship.

One-to-Many cardinality example

A one-to-many relationship refers to the relationship between two entities X and Y in which an instance of X may be linked to many instances of Y, but an instance of Y is linked to only one instance of X. The figure below shows an example of a one-to-many relationship.

Many-to-Many cardinality example

A many-to-many relationship refers to the relationship between two entities X and Y in which X may be linked to many instances of Y and vice versa. The figure below shows an example of a many-to-many relationship. Note that a many-to-many relationship is split into a pair of one-to-many relationships in a physical ERD. You will know what a physical ERD is in the next section.

Conceptual, Logical and Physical data models

An ER model is typically drawn at up to three levels of abstraction:

• Conceptual ERD / Conceptual data model
• Logical ERD / Logical data model
• Physical ERD / Physical data model

While all the three levels of an ER model contain entities with attributes and relationships, they differ in the purposes they are created for and the audiences they are meant to target.

A general understanding to the three data models is that business analyst uses a conceptual and logical model to model the business objects exist in the system, while database designer or database engineer elaborates the conceptual and logical ER model to produce the physical model that presents the physical database structure ready for database creation. The table below shows the difference between the three data models.

Conceptual model vs Logical model vs Data model:

Conceptual data model

Conceptual ERD models the business objects that should exist in a system and the relationships between them. A conceptual model is developed to present an overall picture of the system by recognizing the business objects involved. It defines what entities exist, NOT which tables. For example, 'many to many' tables may exist in a logical or physical data model but they are just shown as a relationship with no cardinality under the conceptual data model.

Conceptual data model example

NOTE: Conceptual ERD supports the use of generalization in modeling the 'a kind of' relationship between two entities, for instance, Triangle, is a kind of Shape. The usage is like generalization in UML. Notice that only conceptual ERD supports generalization.

Logical data model

Logical ERD is a detailed version of a Conceptual ERD. A logical ER model is developed to enrich a conceptual model by defining explicitly the columns in each entity and introducing operational and transactional entities. Although a logical data model is still independent of the actual database system in which the database will be created, you can still take that into consideration if it affects the design.

Logical data model example

Physical data model

Physical ERD represents the actual design blueprint of a relational database. A physical data model elaborates on the logical data model by assigning each column with type, length, nullable, etc. Since a physical ERD represents how data should be structured and related in a specific DBMS it is important to consider the convention and restriction of the actual database system in which the database will be created. Make sure the column types are supported by the DBMS and reserved words are not used in naming entities and columns.

Physical data model example

How to draw an ER diagram?

If you find it difficult to get started with drawing an ER diagram, don't worry. In this section, we will give you some ERD tips. Try to follow the steps below to understand how to draw an ER diagram effectively.

1. Make sure you are clear about the purpose of drawing the ERD. Are you trying to present an overall system architecture that involves the definition of business objects? Or are you developing an ER model ready for database creation? You must be clear about the purpose to develop an ER diagram at the right level of detail (Read the section Conceptual, Logical and Physical Data Models for more details)
2. Make sure you are clear about the scope to model. Knowing the modeling scope prevents you from including redundant entities and relationships in your design.
3. Draw the major entities involved in the scope.
4. Define the properties of entities by adding columns.
5. Review the ERD carefully and check if the entities and columns are enough to store the data of the system. If not, consider adding additional entities and columns. Usually, you can identify some transactional, operational and event entities in this step.
6. Consider the relationships between all entities and relate them with proper cardinality (e.g A one-to-many between entity Customer and Order). Don't worry if there are orphan entities. Although it's not common, it's legit.
7. Apply the technique of database normalization to re-structure the entities in a way that can reduce data redundancy and improve data integrity. For example, the details of the manufacturer might be stored under the Product entity initially. During the process of normalization, you may find that the detail keeps repeating records over records, then you can split it as a separate entity Manufacturer, and with a foreign key that links between Product and Manufacturer.

Data model examples

ERD example - Movie Rental System

ERD example - Loan System

ERD example - Online Shop

Using ERD with Data Flow Diagram (DFD)

In system analysis and design, Data Flow Diagram (DFD) can be drawn to visualize the flow of information within system processes. In a Data Flow Diagram, there is a symbol called Data Store, which represents a database table that provides the information needed by the system.

Since a physical ER Diagram provides a blueprint of an actual database, the entities in such an ERD are aligned with datastores in a DFD. You can draw ERD as a complement to DFD by representing the structure of information that flows within a system, or, on the contrary, to draw DFD in complementing an ERD by showing how the data will be utilized by the system in runtime.

Using ERD with BPMN Business Process Diagram (BPD)

In business process mapping, BPMN Business Process Diagram (BPD) can be drawn to visualize business workflows. In a Business Process Diagram, there is a symbol called Data Object, which represents the data input into / output from process activities.

Since a conceptual and logical data model provides a high-level view of business objects within a system, the entities in such ERDs are aligned with data objects in BPD. You can draw ERD as a complement to BPD by representing the structure of data objects needed by a business workflow, or, on the contrary, to draw BPD in complementing an ERD by showing how the data will be utilized throughout a business process.

Choosing an ERD tool

It takes time and effort to develop a data model with ERD. A helpful database design tool should be able to reduce your time and effort spent. Visual Paradigm provides you with not only an ERD tool but also a set of visual modeling features that helps you draw faster and easier. It supports most of the popular relational database management systems in the market today both in terms of database design, database generation, and ERD reversal.

The ERD designer is available in Visual Paradigm Modeler, which costs only US $6 per month. We would recommend you download and have a try. 30 days of FREE evaluation is offered. No credit card required.

Related Links

1. Learn more about Visual Paradigm's powerful ERD tool