If a picture is worth a thousand words, then entity-relationship diagrams (or ER diagrams) are a priceless way to visually explain the structure and behavior of a software solution. Learn how to make your own ERDs with the Vertabelo online data modeler!
The term entity-relationship diagram and its abbreviations ER diagram and ERD are frequently used when designing and implementing databases. What are these entities and relationships we’re diagramming?
When we mention entities, we usually mean the different objects, persons, or concepts that are required in a system (i.e. the nouns). Relationships describe how entities are associated; we can consider them the verbs. A simple sentence like “An author writes a book” can be easily transformed into an ER diagram, since it includes two entities (author and book) and the relationship (writes) between them.
ER diagrams are usually grouped into three different categories that each represent a level of abstraction. They are known as conceptual, logical, and physical diagrams or data models. I previously explained them in my article What Are Conceptual, Logical, and Physical Data Models; check it out for more details.
When Do You Need ER Diagrams?
Cambridge Dictionary defines a diagram as “a simple plan that represents a machine, system, or idea, etc., often drawn to explain how it works”. Thus, we often find that ER diagrams are widely used when documenting any software solution that stores information in a database. Some of the most frequent use cases include:
- Collecting requirements for a new system or for new features.
- Communicating with businesspeople, e. to explain or validate a database design.
- Documenting an existing software solution.
There are plenty of tools that allow us to 1) design a database by creating ER diagrams and then 2) implement that database by generating SQL scripts to create it. Vertabelo is one of these tools, with the additional benefits of being web based (no need to install any software), very easy to use, and supporting most popular database engines (like Oracle, SQL Server, and MySQL).
Components of an Entity-Relationship Diagram
As the name implies, an ERD needs to reflect both entities and the relationships between them. However, there are different ways (called notations) to represent them in a diagram. Some of the most common notations (all of which we compare here) are:
Different notations are used by different modelling tools, but Crow’s Foot notation (also known as IE notation, Information Engineering notation, or Martin notation) is probably the most widely used in ER diagrams. It’s the default choice in Vertabelo. Tip: If you need to use another notation when using Vertabelo, you can select it in the Model properties section. Read How to Change the Diagram Notation Used in Vertabelo for step-by-step instructions.
Using Crow’s Foot Notation in Vertabelo
Let's look at the main components of Crow’s Foot notation: entities and relationships.
In this notation, entities (tables) are represented as rectangles in Vertabelo. (Some other tools represent them as rounded rectangles.) They include additional details like attributes’ names and their data types, if the attribute accepts nulls, if it is part of a primary or foreign key, etc. All of this is represented inside the rectangles of the entities, as shown below:
The most important components are the:
- Entity (or table) name, shown on the upper part of the rectangle.
- Entity attributes, shown in the lower part of the rectangle. They include:
- The attribute name.
- The data type (based on the database engine selected for the diagram).
- An indicator (PK) used when the attribute is part of the Primary Key.
- An indicator (FK) used when the attribute is part of a Foreign Key.
- An indicator (N) used when the attribute accepts NULL values.
Relationships are represented as lines between entities. Relationships, like attributes, also have additional information that describes the cardinality and obligatoriness of each relationship. Entity-relationship diagrams that use Crow’s Foot notation in Vertabelo represent these two features with different shapes at each end of the line.
There are two symbols for cardinality:
- A straight line represents “one”.
- A “Crow's Foot” or fork represents “many”.
And there are two symbols for obligatoriness (i.e. if the relation is mandatory):
- A ring or small circle represents zero (i.e. optional).
- A dash or short line represents one (i.e. mandatory).
Combined, the symbols define the different types of cardinalities of an entity in a relationship. The symbol closest to an entity represents its maximum value (cardinality), while the other represents the minimum value (obligatoriness).
The image below shows a relationship between two entities (marked in red). Each of the symbols at either end of the line (marked in green) represent the cardinality and obligatoriness of the relationship.
The image above tells us that each customer has one and only one customer type – there is a dash on the line near the
CustomerType entity. It also tells us that each customer type can have zero to many customers; there is a ring and a crow's foot on the line nearest the
There are four possible combinations of symbols:
- Ring + Straight Line “Zero or One”. Minimum zero, maximum one. It means this is an optional (not mandatory) relationship with one occurrence at most.
- Dash + Straight Line “One Only”. Minimum one, maximum one. It means this is a mandatory relationship with one and only one occurrence.
- Ring + Crow’s Foot “Zero or More”. Minimum zero, maximum many. It means this is an optional relationship with many occurrences at most.
- Dash + Crow’s Foot “One or more”. Minimum one, maximum many. It means this is a mandatory relationship with one or many occurrences.
Remember to check our IE / Crow’s Foot Notation article for more details about this notation. Now let see a couple of examples of very simple diagrams in Crow’s Foot notation.
Athletes and Agents
First, let’s see two situations where the relationships are optional. In the sports industry, an athlete may have an agent to negotiate and handle their contracts, but some athletes may decide to represent themselves. Also, an agent can represent zero, one, or many athletes at a given moment. So, let’s see how such a relationship should be represented:
We can see that both sides of the relationship have a ring representing zero (or optional) condition as the minimum. The crow’s foot (many) is the maximum value (the number of athletes an agent can represent) and the straight line (one) is the maximum value representing the number of agents an athlete can have.
Orders and Order Details
Now let’s review a situation where both sides of the relationship are mandatory. When a customer buys products (which creates an order), the order must have at least one detail specifying the product and the quantity desired. At the same time, each order detail must belong to one and only one order. So, we should represent the relationship using these cardinality conditions:
We can see that both sides of the relationship have a dash (representing one, or mandatory) condition as the minimum, plus a crow’s foot (many) in the case of the maximum number of order details an order can have. A straight line (one) represents the maximum value for the number of orders that an order detail can belong to.
How to Create an Entity-Relationship Diagram
Using the Vertabelo Database Modeler, we can easily create an ERD online – without having to install software. Plus, we can easily share our diagrams or generate the SQL scripts required to create a database. Either take just a couple of clicks.
The process is very simple; just click the Create New Document button on the main taskbar to create a diagram in a few steps:
- You need to decide if you want to create a logical or physical diagram. We used a physical ERD in the examples for this article.
- Name your diagram and select your database engine. You can also generate an example diagram to start your model.
Once you have an empty diagram, you can start adding your entities and then establishing the relationships between them. You can get a detailed explanation of all the options in How to Create Physical Diagrams in Vertabelo.
Share Your Thoughts on ER Diagrams
We have just reviewed what an ER diagram is, when to use it, and what its components do. We also learned how to use the Vertabelo Database Modeler to create an ERD. Now it’s your turn: Use the comments section to tell us about your experiences with ER diagrams, share your thoughts on the article, ask any additional questions, or share your database model doubts.