Common ER Diagram Mistakes

Get to know the ER (Entity Relationship) diagram, its parts, and what often goes wrong when creating it.

Have you ever created a relational database model? Or maybe you're trying to create your first one? You know (or you'll soon find out) that translating real-world problems to database logic can sometimes be quite difficult.

One of the tools that might help you is the ER diagram. Common database design wisdom holds that the better your ER diagram, the easier it will be to build the database model. This important item sets the tone for all future frustrations or successes. With a good ER diagram, creating a relational database model is quite straightforward. Of course, mistakes can be made in any phase of database modeling. However, having a good ER diagram can help you avoid some of those mistakes.

So, let's see what the ER diagram is and how we can avoid its common mistakes.

What Is an ER Diagram?

“ER Diagram”, or ERD, is short for Entity Relationship Diagram. It maps out the problem to be modeled, but in a structured way that shows the relationships between entities.

An ER Diagram’s Building Blocks

ER diagrams consist of the following elements:

• Entity
• Relationships
• Entity or relationship attributes

The first element of the ER diagram is the entity. The entity is an object or occurrence that we want to store information about. Basically, it's anything on which we can collect data. For instance, we might store data on employees, students, teachers, buyers, products, departments, payments, locations, etc.

Once we have entities, it is necessary to create relationships. A relationship shows how one entity is connected to and associated with one or more other entities.

The final element of the ER diagram is an entity or relationship attribute. An attribute is a description of a property belonging to an entity or relationship. Attributes have values. Some attributes for the entities mentioned above could be:

• Employee, student, teacher, buyer – ID, name, surname, date of birth, address, etc.
• Product – ID, category, description, color, serial number, etc.
• Department – ID, department name, department head, number of employees, etc.
• Payments – ID, date and time, amount.
• Location – City, ZIP code, region, country, continent.

Types of Relationships

Before we get into the usual mistakes found in ER diagrams, it is important to understand the possible relationship types. Most ERD mistakes are essentially erroneously-defined relationships between entities.

There are three types of relationships between entities:

• One-to-one (1:1)
• One-to-many (1: N)
• Many-to-many (M: N)

One-to-one (1:1) relationships

The first relationship type is one-to-one, or 1:1. In this relationship, a single instance of one entity can be connected only with a single instance of another entity (and vice versa, of course).

Let's say that we have the entity student with the attributes name and surname. We also have the entity id with the sole attribute id. The 1:1 relationship would mean that one student can have only one ID number. It also means that one ID number can belong to only one student.

This relationship is very rarely seen in databases. If only one ID can be connected with only one student, there is no need to separate them into two different entities.

Here's an example of this relationship:

One-to-many (1: N) relationships

The most common type of database relationship is one-to-many or 1: N. A One-to-many relationship means that each single instance of one entity can be connected with multiple instances of another entity. It also means that every instance of the second entity can be associated with only one instance of the first entity.

For example, there is an entity buyer with the attributes id, name, and surname. We want to establish a relationship with the entity payment that has the attributes id, date, and value. This is a 1: N relationship because one buyer can make one or many payments. However, one payment cannot be made by several buyers; it can be made only by one buyer.

Here's the example:

This relationship can also be seen the other way round. In this situation, it is called many-to-one or N:1. Of course, this is not a new type of relationship. It is the same as 1: N, but it is looked at from the opposite direction.

As an example, suppose we have the entity employee with the attributes id, name, and surname. We want to establish employee’s relationship with the entity department that has the attributes id and name. The relationship between these two entities is N:1. This means that every employee can work in only one department, but multiple employees can work in the same department.

Many-to-many (M: N) relationships

A Many-to-many or M: N relationship means that every instance of the first entity can be associated with more than one instance of the second entity. It also means that every instance of the second entity can be associated with multiple instances of the first entity.

Let's see how this works between the entities student and lecture. Let's say that student has the attributes id, name, and surname. The entity lecture has the attributes id and name. A many-to-many relationship can be interpreted in the following way: one student can attend one or more lectures, while one lecture can be attended by one or more students.

Here's the diagram for this example:

In database modeling, such relationships are usually split into two or more 1: N or N:1 relationships by introducing new entities.

Typical Mistakes Made When Creating an ER Diagram

Many ER diagram mistakes fit into one of these four categories:

• Incorrect relationships between entities
• Using an entity instance instead of an entity
• Confusing an attribute with an entity
• Complex attributes

Let’s look at each one individually.

Incorrect relationships between entities

The most common mistakes occur when defining the relationship between entities. There are usually no mistakes in a 1:1 relationship. However, it is very easy to confuse a 1: N relationship with an M: N relationship. This usually stems from not understanding the requirements provided by the end user. It is vital to have very clearly defined requirements and a deep understanding of why the database is needed and how it will be used. If we create an ER diagram with insufficient data and incomplete understanding, it will most probably result in relationships between entities being wrongly defined.

Let's look at an example. If you're creating a database for a bank, you'll most probably create an ER diagram with the entity client having the attributes id, name, and surname. You'll also have an entity called account with the attributes id and type. If you lack experience in the banking industry, you'll probably think there is always a 1: N relationship between the client and account entities, as shown below.

One customer can have multiple accounts in one bank. However, an account can be owned by only one customer. Is this actually true? Maybe it is, maybe it isn't. Quite a lot of banks offer joint accounts that can be used by several clients. Are you creating an ER diagram for a bank that offers such a service? If the bank doesn't offer joint accounts, then you’re right: the relationship between client and account is 1: N. However, if the bank does offer joint accounts, then the relationship is M: N.

Using an entity instance instead of an entity

Another common mistake is using an entity instance instead of an entity. An entity instance is a single occurrence of a certain entity – i.e. an entity that could actually be an attribute of a larger category.

Let's say we work in a company that allocates mobile phones and laptops to certain employees. So, in our database, we’d have an entity called laptop with the attributes id and model and an entity called employee with the attributes id, name, and surname, right?

There’s a problem here: a laptop is actually not an entity – there are also mobile phones to account for. The solution is to replace the entity laptop with a more general entity, such as equipment. This entity could have the attributes ID, type, and model, as shown below. The type could consist of values such as phone, PC, tablet, and laptop. This way, there is no need to create a separate entity for every type of equipment.

You can find the example here:

Confusing an attribute with an entity

The next common mistake is confusing an attribute with an entity. Let's say we have decided to create an entity called employee that will consist of the attributes id, name, surname, date_birth, id_department, name_department, and head_department. This entity will get us in trouble when we’re creating a database model because it consists of too many attributes that don't uniquely define this particular entity.

Remember, we defined entities as anything that we can collect data on. With that in mind, we can see that the current employee entity can be split into two entities: employee and department, as shown below.

Complex attributes

The last common mistake we’ll talk about is including a complex attribute in an entity. In other words, we have an attribute that should actually be its own entity.

Let's say we have an entity called students that’s defined by the following attributes: id, name, surname, date_birth, address, and exam_passed. Here, exam_passed is a complex attribute that consists of more than one piece of information, i.e. the exam ID and date and the student’s score. Leaving it that way would be a mistake. Instead, we should make a new entity out of this complex attribute. The new entity could be named exams and consist of the following attributes: id, date, students_id, and score.

You can find the example here:

Did You Get Any Useful ER Diagram Tips?

These four types of mistakes are the most common ones in the ER diagram creation process. Of course, there's no complete list of typical mistakes or types of mistakes. In real life, many kinds of mistakes are likely to happen. A lack of planning, insufficient technical support, and a rushed database design process all contribute their own problems. If you’ve ever created databases or participated in it from the business side, you’ve probably experienced some of them! All those circumstances lead to various mistakes, some of which are quite unique.

Do you have your own example of a not-so-good ER diagram? Or maybe there are some other mistakes that you find frequently? Please let me know in the comments section.