An entity relationship diagram is a graphical representation of entities such as people, objects or concepts and how they relate to one another.
ERDs are most often used in relational and dimensional database designs, such as transactional, data warehouse and data mart designs. They can also be used to convey business information systems, educational purposes and research.
What are the 3 types of relationship diagrams?
- Conceptual data model
- Logical data model
- Physical data model
Why Make an Entity Relationship Diagram (ERD)?
ERDs are imperative for businesses to document their processes. Primarily, professionals use ERDs to aid in business communication, database design, modifying and maintaining databases and query writing.
Business Communication
ERDs allow business users to view business processes graphically, making it easy to use when capturing business information. Any business process that uses data fields or involves actions and interplay can benefit from an ERD.
The ER conceptual model helps convey business processes by representing a structured business view of data required to support business processes. This model focuses on identifying the data used in the business but not its processing flow or physical characteristics. For example, it allows business people to view specific business subject areas such as patient data, campaign data, content data and any other business subjects that are in the integrated model and outside of the applications themselves.
Database Design
For a good database design, an ERD is a must-have, as they help ensure that business rules are captured when storing data in a database. Database design uses all three ER models.
Modifying and Maintaining Databases
ERDs can help businesses document existing databases even when there isn’t an ERD available. Reviewing an existing ERD can help businesses troubleshoot existing logic or help spot inefficiencies that can later be modified to improve business processes. The process of creating an ERD from a database is called reverse engineering. Businesses use specific tools for reverse engineering, such as Erwin, DbSchema, Toad and more.
Another technique used for modifying databases is called forward engineering. This is when a script is generated from an existing or new physical data model to execute on the database.
Query Writing
When querying the database, it is beneficial to understand the structure of the tables and its relationships. ERDs help designers quickly locate the tables, and their related tables, needed for query.
Components and Symbols of Entity Relationship Diagrams
To read and create ERDs, we must first become familiar with the vocabulary and types of relationships we use to describe them.
Attribute
An attribute is the field/content associated with an entity — the defining characteristic of an entity. Attributes are usually used in the conceptual and logical models.
Entity
An entity is a rectangular object normally used in the conceptual and logical model to represent an object that stores data in a database system. There are two types: strong and weak.
The strong entity does not depend on any other entity and can stand on its own, while the weak entity depends on other entities, because it cannot be uniquely identified by its attributes alone.
Foreign Key
A foreign key references a primary key defined on another table.
Primary Key
A primary key identifies a record in an entity or table. A good practice is to make sure that every entity has a primary key defined.
Relationships
The relationships used in an ERD indicate how entities/tables are related to one another. When creating data models, it’s important to understand the different types of relationships that can be used in a data model. Here are the types of relationships.
Unary Relationship
The unary relationship, also known as the recursive relationship, is when there is only one entity involved. Essentially, the entity is related to itself. A good example is an employee entity.
One-to-One Relationship
A one-to-one relationship is when there’s a record in one table that can only have one entry (record) in a table it’s linked to. A good example is an employee and the car they drive.
One-to-Many Relationship
A one-to-many relationship is when a record in one table can have one or multiple entries in the other table it’s linked to. An example is the relationship between a store and department. A store can have multiple departments, but the department cannot have multiple stores.
Many-to-Many Relationship
A many-to-many relationship is when a record in one table can have one or multiple entries in a table it’s linked to, going in both directions. Take the relationship between a store and customer. A store can have one or multiple customers and the customer can have one or multiple stores, because the customer can shop in multiple stores belonging to the company. A good practice is to resolve the many-to-many relationships to avoid issues down the road by introducing a resolving table.
Table
Tables are database objects that contain data in a database. In tables, data is logically organized in a row-and-column format like a spreadsheet. The term “table” is only used in a physical data model, while “entity” is used in a conceptual or logical data model.
Uses of Entity Relationship Diagrams
There are different types of ERD models, also known as data models, that depict data at different levels of abstraction. The range includes the conceptual data model, logical data model and physical data model.
Conceptual Data Model
The conceptual data model is a high-level abstract graphical representation of data showing relationships between business entity concepts. It provides high-level information without diving into technical details, showing only the business entities and how they are related. Here are the four main benefits of the conceptual data model.
- The conceptual data model is the foundation for the other two models, logical and physical.
- It helps validate how concepts and entities are related to one another, making it essential for data requirement gathering — especially when communicating with a business team.
- It helps define a common language that can be used to create a company’s business glossary.
- It’s the focal point for discussion with other teams, such as stakeholders and architects.
Logical Data Model
Like the conceptual model, the logical data model illustrates the relationships between entities and how it will be implemented without any specific database system in place. Unlike the conceptual model, it’s more detailed, defining the structure of each entity by adding attributes, constraints (business rules) and any other rules needed for implementation. Here are two primary benefits of the logical data model.
- The logical data model helps identify business process improvements.
- It provides a basis for future models.
Physical Data Model
The physical data model illustrates how the system will be implemented and the specific database management system that will be used. Similar to the logical model, it’s usually created by developers. Tools like ErWin and SQL Developer will automatically generate the model based on the logical model created.
The physical model captures information about how the objects (entities) will be implemented, such as the database system that will be used, data types for each of the fields and constraints (business rules).
ER Diagram Example
Here is a sample of a conceptual data model.
And here is an example of a logical data model.
ER Diagram Best Practices
When creating ERDs, there are a few best practices I always follow: making sure my ERD is accurate, consistent, simple and transparent.
Accurate
This is the most important principle. The diagram should be accurate, capturing all business relationships and information and ensuring that all entities, attributes and relationships are correctly identified and represented in the diagram.
Consistent
You must be consistent with the symbols, notations and when naming objects. Each tool has a different way of representing relationships and entities. Ensure that your team or organization sticks to one symbol and notation so it’s consistent throughout. You should put a naming convention in place for how each object should be named. Some organizations use data governance for this, but otherwise it’s the responsibility of the data modeler.
Simple
The ERD should be simple and easy to read to help avoid confusion.
Transparent
When modeling and creating an ERD, the model must be clear and easy to understand. The fields and entity labels should represent the information being captured.
Entity Relationship Diagram Tools
There are lots of options for tools to use when creating an ERD. Below are the top five I recommend.
Db Schema
Visual and easy to use, DbSchema can help you visually design and manage databases in a team, generate HTML5 schema documentation, build SQL queries using the mouse, generate random data, explore data from multiple tables, edit and execute database queries.
ER/Studio
ER/Studio combines Data Architect, the industry-leading data modeling software, with Team Server, the server-based repository and web portal. The result is powerful database modeling tools that empower organizations to design data assets that drive business strategy and success. ER/Studio also integrates with Collibra data governance and major data governance tools.
ErWin Data Modeler by Quest
The ErWin Data Modeler can be pricey, but it’s my personal favorite due to all the features it has. It’s an award-winning data modeling tool used to find, visualize, design, deploy and standardize high-quality enterprise data assets. You can use it to discover and document any data from anywhere for consistency, clarity and artifact reuse across large-scale data integration, master data management, metadata management, big data, business intelligence and analytics initiatives, all while supporting data governance and intelligence efforts.
Sql Developer Data Modeler
Oracle SQL Developer Data Modeler is a free graphical tool that enhances productivity and simplifies data modeling tasks. Using this tool, users can create, browse and edit logical, relational, physical, multidimensional and data type models. It provides forward and reverse engineering capabilities and supports collaborative development through integrated source code control, and can be used in both traditional and cloud environments.
Toad Data Modeler
Toad Data Modeler allows you to visualize database structures and better understand existing databases and relationships to create high-quality logical and physical data models. With this tool, you can reverse engineer existing database structures to view them in a diagram and generate detailed HTML, RTF or PDF reports.
