A picture says a thousand words. Turns out, it can also be a handy tool for when you’re migrating large datasets or configuring a new database for implementation.
The thing about data is that it isn’t really there. It’s just values; measurements, descriptions and such used to quantify or qualify something. Most of the time, this isn’t a problem. You can’t move for data. It’s part of our everyday language, so generally speaking, we all get it.
But when the conversation starts to get technical — for example, how a piece of data is structured, its contextual relevance, or utility — understanding starts to drop off, and your audience with it (stay with me). This is especially true when you’re working with lots of data.
But understanding these things, and the relationships between different objects in a database, plays an important role in business today. In this area, ERDs can help.
“ERDs, or entity-relationship diagrams, are visual representations of the entities in a database and the relationships between them,” says Hannah Anslow, our CRM consultant.
“In database design and software engineering, ERDs are commonly used to model the structure of a database,” adds our solutions architect, Raymond (Ray) Boateng.
Between them, Hannah and Ray have navigated their way around more than a handful of ERDs on their missions to configure fit-for-purpose HubSpot CRMs for their clients. What problems does an ERD solve? How do you create one? And why do we build them into our technical implementations? I sat down with Hannah and Raymond to find out more.
“Your success rides on the way your database is structured”
The relationships between different objects is especially difficult to see. Challenges arise when your business demands change and you need to optimise your database setup. When this happens, it becomes much more important that you know how your database is set up to work because you need to either optimise it, recreate it in a new system, or both.
“The success of your migration or implementation is riding on the way your data is formatted and structured,” Hannah explains, “never mind the disruption to operations if you don’t have a handle on it. You need clear visibility over all the entity relationships in your database.”
There’s a lot that can go wrong with a migration or implementation but there are also opportunities to be unlocked: the aforementioned functionalities, new integrations, stronger inter-departmental relationships. Forecasting. Reporting. Revenue generation.
“A technical database project can quickly become stressful, but it’s also your opportunity to fix everything that hasn’t been working in regard to your data management,” Ray adds. “You can use an ERD to achieve this by giving you visibility over the way your data is set up, how it interacts with other objects or data sets, and how to improve it.”
Related read: Data Management 101: How to Choose the Right Data Categories
One too many: different ERD relationships explained
The act of mapping out an ERD is relatively straightforward and can be readily accomplished using your preferred design software (we often see Lucidchart used). But what do they actually look like and how can you best set them out to highlight your object relationships?
“In an ERD, entities are represented as rectangles, and relationships between entities are represented as lines connecting them,” Hannah explains. “Multiple lines equate to multiple relationships. Single lines to single relationships. Each entity then typically has attributes, or properties, associated with it, listed within the rectangle representing the entity.”
According to Ray, there are several types of relationships that can be depicted in an ERD.
“A one-to-one relationship is a single instance of one entity being related to one instance of another entity. A one-to-many relationship is a single instance of one entity being related to multiple instances of another entity. A many-to-one relationship is multiple instances of one entity being related to a single instance of another entity. And a many-to-many relationship is multiple instances of one entity being related to multiple instances of another entity.”
ERD relationship examples
For this example, we’re taking a trip back to school. You work at a local university, where the admissions team is preparing the university’s CRM system for a migration to HubSpot. Working with a specialist solutions partner (ahem), they are mapping out an ERD that will enable them to configure HubSpot CRM in a way that will work best for the university.
As they work through their data, the people on the project are identifying examples from each of the different types of entity/object relationships, as follows:
One-to-one (1:1)
A Student has one Student ID, and each Student ID is associated with only one Student. This is an example of a one-to-one relationship between the relevant entities.
One-to-many (1:N)
A Department has many Courses, but each Course belongs to only one Department. This is a great example of a one-to-many relationship.
Many-to-one (N:1)
Many Students belong to one Faculty Advisor, but each Faculty Advisor advises multiple Students. Because more than one student can be advised by the same advisor, but each student has only one advisor, this is a many-to-one relationship between these entities.
Note that these two relationships (1:N and N:1) are essentially examples of the same type of relationship, just viewed from a different perspective, with the focus on the singular entity and the many entities respectively.
Many-to-many (N:M)
Many Students enrol in many Courses, and each Course has many Students enrolled. This is a many-to-many relationship between the Student and Course entities.
Making a business case for ERDs
ERDs play a vital role in database design and development. They are commonly used during this phase of database projects to plan the structure of the database schema. Database designers and developers use ERDs to define the entities, attributes, and relationships that will be represented in the database, with the added benefit that they can help stakeholders visualise the database and understand how different entities are related to each other.
Gathering and documenting requirements
“They can also be used as a tool for gathering and documenting requirements from stakeholders,” Hannah says. “By creating an ERD, you can elicit requirements related to the entities, attributes, and relationships that need to be included in the database, making it easier for stakeholders to provide feedback and validate the requirements.”
Communication and collaboration
They have a communication and collaboration role between project team members, including database designers, developers, business analysts, and stakeholders. By providing a common visual language for discussing the structure of the database, it becomes much easier for these different people, who otherwise speak very different languages, to communicate in common terms on all aspects of the ERDs/database design.
Their role in database schema
Ray highlights their application “as a way to document the database schema”, providing a visual reference for future maintenance and development efforts. “Database administrators and developers can refer to the ERD to understand the structure of the database and make informed decisions when making changes or enhancements,” he explains. “ERDs help ensure consistency and accuracy in the documentation, reducing the risk of errors.”
Data governance
Finally, there’s the data governance angle. ERDs can support this initiative by documenting how data is structured and organised within the database. They provide a standardised format for helping your organisation to achieve compliance, ensuring that data is managed and used in accordance with regulatory requirements and your own internal policies.
You might also be interested in:
Our quest to map your objects and optimise your database
ERDs help developers and database administrators visualise the structure of a database and understand how different entities are connected. They serve as a blueprint for designing the database schema and can help ensure that the database design accurately reflects the requirements of the system being developed, as well as a wide range of other applications.
As part of our Expert Practices team, Hannah and Ray are well-versed in both the project importance and the practical delivery of ERDs with which we can help our clients to unlock the full potential of their HubSpot portals. Our ERDs play a crucial role in database design and development projects by facilitating requirements gathering, communication, collaboration, documentation, and compliance, for both our team and our clients’.
A picture says a thousand words. Turns out, it can also be a handy tool for when you’re migrating large datasets or configuring a new database for implementation. With an ERD in place, even the most elusive data can be captured with a few digital brushstrokes, bringing clarity to your entities, understanding to your objects, and a clear map for everyone to follow.
.png?width=380&height=429&name=UBDS.drawio_(1).png)
