Connecting the Dots: Exploring Entity-Relationship Diagrams for Effective Data Modeling

Photo by Ross Sneddon on Unsplash

When we were children, we all enjoyed playing with puzzles. Similarly, our database architecture is like a puzzle game. In the world of data, each piece of information is like a puzzle piece that plays an essential role in creating the complex tapestry of database architecture.

The ERDs (Entity-Relationship Diagram) are considered one of the heroes of effective data modeling (a whole article about data modeling here — feel free to read it 😁).

First of all, let’s understand what an ER Diagram is

ER Diagram = Entity-Relationship Diagram (we’ll use the abbreviation for simplicity)

Based on Google results, an ER Diagram is

a specific type of data model used to represent the entities in a system and the relationships between them. ER diagrams use a graphical representation to illustrate the structure of a database.

The ER diagram is composed of 4 elements:

1. entities — the well-known tables of a database (e.g. customers, employers, and so on)
2. attributes — are the elements or columns inside an entity (aka table); for example, if you have an employee table the attributes you’ll most likely have are: the employee name, position/job title, salary, hire date, and more, based on your requirements
3. relations — this model is used in what we call relational databases and represents, as the name suggests, relationships or connections between entities (tables).
4. cardinality — represents the type of relationship, which can be: one-to-one, one-to-many, or many-to-many (but that’s a topic for another article 😉)

When are they used?

ER diagrams are useful in the conceptual and logical phases of data modeling (i talked about it a while ago here). They provide a visual representation that helps stakeholders (technical and non-technical) understand the structure of data and how different entities are related.

Even though this is often forgotten, the ER diagram plays the role of documentation that helps the developer perform a clean and fast change process of the database because by having an ER diagram they can easily analyze the impact that the requested changes will have on existing entities and relationships.

It also helps improve query performance and data governance by understanding the relationships between entities and ensuring integrity by defining constraints (which helps maintain consistency).

Good, now…Let’s see it in action

You’ve recently been brought on board as part of their R&D team, as a database developer, by a growing company to enhance their HR processes. After the first meeting, you found out that till now they relied heavily on Excel files. Because of that, they have problems with data inconsistencies, difficulties in generating reports, and many security issues. Fortunately, aside from the above details, you received an Excel file containing crucial employee information and the opening of the stakeholders to a new centralized database that can efficiently store, retrieve, and manage employee information.

Your role in creating a robust database system has begun, and now it's time for you to analyze the Excel file and schedule meetings with stakeholders to gather additional requirements and clarify things.

Let the show begin and put your skills to work 🤓

Now that most mysteries have been elucidated, you start the database design by creating the ER diagram. Based on the Excel file below are some of your entities:

Identified entities based on the received file

Your work so far has been impressive. Your analytical and communication skills are amazing 👌

Going further, you need to establish the attributes (columns) and also relationships and cardinality for each entity (table); also based on the file received from the client you identify the following logical data model:

Complete ER Diagram (made using diagrams.net)

Relations and Cardinality:

• one-to-many: used between tables such as Employee and Position, Employee and Department, Employee and LeaveRequest, Position and JobApplication entities (because there can be many Employees that have the same Position, also a Department has many Employees, one Employee has many LeaveRequests, and there might be many JobApplications for one Position)
• many-to-many: used between Employee and Training tables and represented by creating an additional table named EmployeeTraining, because multiple Employee can participate in multiple Trainings.

Upon sharing the diagram with the stakeholders, a sense of amazement visibly spread across their faces. Without any hesitation, they promptly initiated their work, relishing every moment spent in the pursuit of their objectives.

Easy, right?

Conclusion

Now you have seen that ER diagrams are versatile and very easy to understand even by non-technical persons. They come in handy in the database design process, leveraging communication, documentation, and the most critical aspect, the maintenance of data integrity and quality.

Remember, data modeling is a broader concept that includes various models, but an ER diagram is a specific type of data model used for visualizing the relationships between entities in a system or database.

Stay tuned and also, do not forget you can find other interesting data-related articles on my Medium.

If you liked my articles, let’s spend our coffee break together here🤗. Thank you for your support!