How to create Entity Relationship Diagram

A colleague who works as a backend engineer once told me that everytime he starts working on a project he just needs to draw an ERD and he knows what he has to do. I was in doubt at first but once I started creating one (real one, not just drafting on paper), I finally understood.

1. Entity Relationship Diagram (ERD)

ERD provides engineers a way to visualize how information in a database is related and how it works together.

Take online shopping as a reference, you can see that there are many things that we need to keep track of (such as product, shipment, customer,…), or what happens when a customer purchases a product. If someone has just joined the team, just by looking into the database, it’s hard to recognize how each data is saved and how they are connected. That’s when ERD comes in handy.

2. Basic components

a) Entity

Entity is simply the object that we want to keep track of in our database. It could be people, places, things, etc.

For example, if you are a headmaster of a school, you might want to keep track of the class, the teacher, the student, the subject, the score, etc. Then, you would have these entities: Class, Teacher, Student, Subject, Score.

b) Attribute

Each entity will have attributes. These are properties or traits of an entity.

With the same example above, what information we need in a class will be its properties and the same for Student, Score and Subject. Each class will have “name”, “number_of_students” and “person_in_charge”. Each student will have “first_name”, “last_name”, “class” (that he/she belongs to). And so on.

c) Relationship and Cardinality

Relationship describes how entities interact with each other. There are 3 kinds of relationship:

• One-to-one: one entity (A) can only link to one other entity (B).
• One-to-many: one entity (A) can link to many other entities (C,D,E,etc. Each of these entities can only link to A ).
• Many-to-many: Many entities can link to many other entities. In this case, there will be another entity that acts as an intermediary.

When we draw a line to define connection between entities, there are symbols used to demonstrate the relationship in a numerical context. These are called cardinalities.

3. Steps to create an ERD

After keeping those basic components in mind, all you need to do is ask the right question in order to create an ERD.

• To know how many entities we need, ask: “What are the objects that I want to keep track of?”
• To know the properties of each entity, ask: “What information do I want to know about that object?”
• To define relationship between 2 entities (Class and Student, for example), ask these questions:
  • “What is the minimum number of “Student” that a “Class” would have?”  (Answer: 1)
  • “What is the maximum number of “Student” that a “Class” would have?”  (Answer: many)
  • “What is the minimum number of “Class” that a “Student” would belong to?” (Answer: 1)
  • “What is the maximum number of “Class” that a “Student” would belong to?” (Answer: 1)

After done, your 2 entities would look like this.

If you take a closer look, you can see that I marked PK and FK on the entity. Primary key is the unique key that represents a record in an Entity (A, for example), and foreign key is the primary key of another Entity (B, for example) that is used in this Entity (A). Well, it’s just a way to say that the “class” attribute in Student entity is referencing a record of Class entity.

Any attribute could be primary key as long as it:

• is unique (identified with only 1 record in our table)
• is a constant (never be changed)
• never be null

You can also add data type to make it clear for the development team if you want to. Even if you do not, your ERD is already good for visualizing the database.

That’s it, creating ERD is very simple, yet making database relationships visible. Some of the apps you can use to create ERD are Lucidchart, draw.io, miro,… They are all free to use and easy to start with.