If you’re looking for a way to define how data is set up in your business analysis system, you’re going to love the entity relationship diagram (ERD). The ERD helps you organize and document the various data entities and their relationships to one another within the project. The ERD is primarily a tool to help you communicate with the data analyst on the project.
Introduction to entity relationship diagrams for business analysis
Like other analysis techniques, the ERD uses simple boxes, lines, and symbols to diagram the entities, attributes, and data relationships. The entities are the uniquely identifiable people, things, or concepts whose information is important to the business; the attributes are distinguishing characteristics of the entity; and the relationships explain how the entities share data.
You should apply this technique in situations such as the following:
When you want to understand or demonstrate the relationships between the data. The relationships define business rules you’ll need to know about.
When communicating the data requirements to the data analyst or developer.
When you need to understand all the attributes of each data element.
The image below shows three boxes that represent the three entities involved in the shopping cart: the order, the line item, and the product. The relationship line has three symbols — the crow’s feet, the circle, and the straight line — that tell you exactly how those entities relate, with a minimum and maximum value for each side. These relationships are called cardinalities:
The crow’s feet symbol indicates multiple entities, such as multiple e-mail addresses for one employee. You can use this symbol to show a maximum value only.
The circle symbol denotes that the item is optional — that a connection isn’t required. For instance, an employee may not have a home phone number (with the advent of mobile phones, that situation is becoming more common). You can use this symbol to show that a minimum value of zero is okay.
The | (vertical bar) symbol indicates a single entry that can be used as both a minimum and a maximum value. For example, if you need a person’s name, that person will have one and only one first name. This symbol shows that he must enter a first name and has a maximum of one name only.
As an example, read the relationship between entities Order Line Item and Product by looking at the line between the two from left to right. The entity Order Line Item contains / is on (the first verb on the line) one (the | on the inside of the line, signifying the minimum number) and only one (the second | on the line, signifying the maximum) Product.
To read the relationship from Product to Line Item, simply reverse the process from right to left.
Here are a few pros and cons of using this technique to consider:
Pro: The ERD is a highly accepted standard in the data analysis world, and data analysts and developers are very familiar with how to read it.
Pro: It’s a clear description of how the data entities relate and the minimum and maximum cardinalities they exhibit.
Con: Business people may not be familiar with the standard and may not be able to validate it without understanding the symbols.
Con: Getting a cardinality wrong may throw off the solution design.
Here’s how to create your ERD:
Elicit from your stakeholders the information they need to capture within the scope of your project.
Group together the subentities and the attributes that make up the entity.
Draw (either in Microsoft Visio or freehand) the rectangles for the entities; put the appropriate entity name on each, and put the attributes inside.
Determine how you connect the entities (the relationships) and draw those in.
Validate your diagram with your stakeholders.
How to present the data in your business analysis with entity relationship text templates
If your stakeholders (or even you) aren’t comfortable with the symbols and drawings in ERD, you can use a text template instead. The entity relationship text template details the very same ERD information but in a text table, using a relationship between two entities as the key. You can read across each row based on the pairing and come up with the relationship.
The advantage with this approach is that you don’t need to learn the database modeling symbols, and you don’t need Microsoft Visio to draw this diagram (as you may with the ERD). The disadvantage is that data analysts or developers may balk at receiving the text template.
How to round out the data in your business analysis through entity text templates
The ERD and relationship text template are good, but they still don’t paint a complete picture. Whether you use an ERD or entity relationship text template, you always need to document additional information about the diagram in an entity text template.
Entity ID: The identifier for the entity, used for tracing and reference. You create this ID as you create the tracing.
Name: The name of the entity. What does the business call this entity? Using the same name helps communication.
Unique identifier: The ID or key that uniquely identifies an instance of the entity. For instance, the unique identifier for the entity of Order in a web-ordering project is Order-ID.
Number of occurrences: How many occurrences exist today and how many that number is expected to grow by in the future. The business can probably tell you how many it has now (or if it’s an existing data element, the technical people can tell you how many exist in the data structure) and what the projections are.
Owner: Who owns this data? Determining ownership helps identify who makes decisions about changes in occurrences and expectations of growth.Credit: Illustration by Wiley, Composition Services Graphics