An Entity Relationship Diagram (ERD) is a data model describing how entities (or concepts or things) relate to one another. When created by business analysts or business users, ERDs can be used to understand the business domain, clarify business terminology, and connect business concepts to database structures.
(By the way, if you are looking to learn more about data modeling, be sure to check out our Free Data Modeling Training.)
Essentially, a conceptual or logical ERD will visually show how the terms in your glossary relate to one another. They are especially helpful in clarifying information models for relational databases and helping business users understand database structures at a high level and without details.
(This might surprise you as typically ERDs look almost ridiculously complicated. That’s because most ERDs are automated output from physical database designs, not carefully crafted abstractions of business concepts.)
In this tutorial, you’ll learn what goes into an ERD and how to create one from a more business-focused perspective.
For those who like to read instead of watch, here’s the full text of the video:
What is an ERD (Entity Relationship Diagram)?
Hi, I’m Laura Brandenburg from Bridging the Gap, and today we’re going to talk about ERDs, or entity relationship diagrams, and specifically how to do them from a business perspective, and why they’re really valuable for business users and business analysts. What is an ERD? An ERD is a data model that describes how entities or concepts relate to one another. So, those are two concepts with a line to create.
We’re going to show you a model, an actual example, before I close the video so that you’ll have more than my fists with a line between them. But that is essentially it. We over-complicate them because what often happens is the first ERD we ever see is a messy spider web of boxes and arrows and lines and details and stars and all kinds of things. We’re like, “This is crazy complicated. It makes no sense.”
The reality is that most of those, if not all of those, are automatically generated output showing the physical details of a database, as opposed to carefully designed abstractions of concepts that help us conceptualize, talk about, and realize how business concepts relate to one another. It’s the same exact syntax and semantics and modeling technique, the same modeling technique, but it’s done in a very different way. We’re going to talk about how to do it from a business focused way that looks at the very high level business concepts and the relationships between those.
Really what you’re doing in an ERD is you’re showing logically how concepts relate to one another and what the key data elements are that you need to know about a concept as a business user. It’s a tool to help really communicate how information is stored and how the business thinks about relationships.
What would a business concept be, just as an example? It could be customer order. Customer order is a good one. Can a customer have multiple orders, and can an order have more than one customer? Usually not. Usually a customer can order multiple times.
That’s awesome, but usually an order has to have one, and only one, customer. That’s what we mean by relationship.
It could be could job seeker, another example we use pretty often. A job seeker and their résumé that they can use to apply to a job. Then a hiring manager looks at that application and decides to contact them. Some of that got into flow, but how does a hiring manager and a job seeker and a job posting and a résumé, how do those concepts relate to one another? Can one job seeker have more than one résumé? In some systems, yes. In some systems, no.
It’s a question. Maybe there’s a one-to-one relationship between a résumé and a job seeker. Maybe there’s a one-to-many relationship, and then when they go to apply for a job, they have to decide which résumé they’re choosing to submit for that application.
Those are the ways that we’re talking about. What are those core business concepts, and how do they relate together? This is what an ERD will show from a business perspective.
Now from a data perspective—let’s just talk about that for a tiny little bit—this is not the way that we teach them at Bridging the Gap, and it’s not the way that most business analysts need to create them, but it’s going to show, at a very detailed level: what are all the tables in the database, what are the relationships between those tables? Again, it’s the same exact idea of what we just talked about: one-to-many, one-to-one, many-to-many. What are the relationships between those tables, and what are all the fields that are included in all of those tables?
It’s the physical—it’s actually how the database is built, the physical structure of the database, which is important. We need that, but we don’t always—there’s a lot that goes into the physical structure of a database that is implied or not relevant from a business perspective, and when we show all of those details to our business users, we get, “That’s a messy spider web I know nothing to do with,” versus if we can winnow it down and show a very intelligent, thoughtful abstraction of that information, we can get some really important business input and clarity on business concepts that will help us build the database that will actually serve them better.
Key Elements of an ERD (Entity Relationship Diagram)
So what’s included in an ERD? We’re going to show the visual model here, and I’m going to talk you through some of the key examples. First are the entities. The entity is the thing, the concept. It’s the box on this model that you’re looking at. In business domain terms, it’s a concept. In relational database terms, it’s going to be the table.
Then, there are the relationships. They’re the relationships that connect those boxes. This is where the real insight from this diagram comes because we see how those entities relate to one another. How does the job posting relate to the résumé? How does the job seeker relate to the résumé? Relationships are really those verbs or the numerical relationship that links those nouns together.
Finally, we have attributes. Attributes are the details within each entity, and there can be more than one attribute. They provide detailed information about the concept. If we’re having a job seeker, we need to know their name. We need to know the date they joined, maybe. We need to know their current employment status. Some key attributes or information that we would store in this concept of job seeker.
By the way, you have this sample here. I’ve also included the sample and the Visio swipe file of this along with twenty-one other actual visual models in our Visual Model Sample Pack. It has twenty-two visual samples, one of which is the swipe file that you’re looking at here. There are other swipe files in Excel and PowerPoint and Visio. Then we also, along with that, have a guide for how to create each model. It’s not just a swipe file. It’s: why did I create it? How did I create it? How do you create it? When would you use it? Why is it important as a business analyst? Lots. A “how-to” practical crash course type information in these things.
How to Create an ERD (Entity Relationship Diagram)
But I want to talk a little bit more about that here, specifically on the ERD, or the entity relationship diagram. How do you actually create this thing as a business analyst? What do you do?
Usually, I will start with the glossary that I have, if I have a glossary. If I don’t, I’ll start pulling the nouns out of my use cases, pulling the nouns out of my business process documents, or pulling the nouns out of any requirements document that I have, and pulling those together and putting them in boxes on the page.
I don’t often do it right in Visio. I, more often, will start on a blank sheet of paper and draw because it’s really messy, or I’ll do it on the whiteboard and start to piece these things together because you’re going to do it, and you’re going to redo it. Then you’re going to look at it, and you’re going to have lines crossing, and you’re going to need to move things around. If you’re trying to do it in Visio, there’s so much to figure out in Visio that it takes away from the headspace you need to figure out, “What are the concepts that I need to cover, and what are the relationships that I need to cover between those concepts?”
So, I just start with those nouns, piecing them together, and then start looking at, “Does this one have a relationship to this one? If so, what is that numerical relationship? Is it one-to-many? Is it many-to-one?” I talk myself through that relationship, then I put the line in with the appropriate notation to cover that relationship. There are two notations. I didn’t mention this before. There’s the crow’s foot notation, and there’s the multiplicity syntax, which has a numeric value. Crow’s foot represents that visually with crow’s feet at the end or one line, where multiplicity shows it in numbers. You can use either one. We include examples of both because it’s really a personal preference. Whatever your organization uses, be consistent.
Then, I would piece it together. I would get a draft together, and one of the more powerful experiences I had reviewing an ERD and using it, not just to be like, “Hey, look at me! I have this ERD,” but to actually say, “We have a fundamental challenge on this project of how we’re going to realize what the business wants in our physical database model.” We had this disconnect, and everybody was like, “It’s impossible to do what they want in the physical database.”
We were just going back and forth, and the use cases weren’t getting to enough detail, or they weren’t getting these relationships. We were looking at one slice here and one slice here, so I took a holistic view of all the concepts and all the relationships that we were trying to model and realize in the system.
Then, I sat down with my primary business subject expert and our primary development expert, and I gave them a printout of what I’d been working on, but I was like, “This is just an idea. It’s just a draft.” I asked the developer to withhold, step back from what the database does and step in to a space of understanding what the business wanted. I said, “I realize this might be tough. It might be infeasible. We might not ever be able to build this, but just for the purposes of this next hour or two (it was probably a two-hour meeting) please just suspend that attitude and show up to just understand what they want. Then, we’ll worry about the solution later. I promise I’m not painting you into a corner here, but let’s just get aligned on what we actually want.”
Then we proceeded to redraw this on the whiteboard, and it was beautiful. The model I created was complete crap at the end of this, but it was a talking point that helped me prepare because I was the least knowledgeable person in the room about this domain.
So, it helped me prepare to create that model, and it helped me make sure we covered everything. By having that draft put together, it helped them see where I was going as we were drawing things on the whiteboard. Ultimately, the whiteboard represented a completely different set of concepts. Not completely different a very different view, and it was a complete view where everybody could look at that and be like, “Yes. This is what we want the system to do.”
The magic of that is that within hours, or maybe even days, or maybe even in that meeting, the developer was like, “This is how we can do this, and this is how we can model this. We just need this little tweak here and to adjust this relationship here. It’s really not a big deal.”
It went from being this huge problem on the project to being, “It’s really actually not a big deal.” That model helped us get over that intellectual hurdle in that project, and it was an ERD, and it was a business-focused ERD.
This is why we use them as business analysts. This is a tool, and it’s really useful, even to just help us think through these concepts so when we’re writing a use case, we’re thinking about the steps. Does that align with our ERD? Does the data model, the information model we’re asking for actually support what we want the system to be able to do? Are there variations in the business process? These all go together, in terms of different views to look at the requirements.
Again, I invite you if you’re interested, download our swipe file. It’s part of our Visual Model Sample Pack. You’ll get the ERD plus twenty-one others.
Thank you for watching. Thank you for taking the initiative to create one of these ERDs yourself. They are not scary. I promise. Again, I’m Laura Brandenburg at Bridging the Gap, and we help you start your business analyst career.
>>Learn More About Data Modeling
Learn the essential Data Modeling Techniques (even if you don’t know how to code) with this free training.