An ER diagram (entity-relationship diagram) is a visual model of the data in a domain that shows the entities to be stored, their attributes and the relationships between them, including how many instances on each side can be connected. In data modeling for databases, it is the standard tool for agreeing on structure before any table is created, and it serves as living documentation of the schema afterward.
The entity-relationship model was introduced by Peter Chen in 1976. Since then, several notations have evolved from it, and the most widely used in practice is crow’s foot notation, which most modeling tools and database IDEs generate. Whatever the notation, an ER diagram answers the same questions: what things matter, what is known about them, and how they relate.
Building blocks of an ER diagram
An ER diagram is built from three core elements: entities, attributes and relationships, refined by cardinality and participation constraints. The table defines each element and how it later appears in a relational database.
| Element | Meaning | Example | Becomes in the database |
|---|---|---|---|
| Entity | A distinguishable thing the business tracks | Customer, Order | Table |
| Attribute | A property of an entity | email, order_date | Column |
| Key attribute | Attribute that uniquely identifies an instance | customer_id | Primary key |
| Relationship | A meaningful association between entities | Customer places Order | Foreign key or junction table |
| Weak entity | Entity identified only through its owner | Order line within an Order | Table with composite key including parent key |
Attributes have subtypes that matter during design. A composite attribute such as an address splits into parts, a multivalued attribute such as phone numbers usually becomes a separate table, and a derived attribute such as age is computed from stored data rather than stored itself.
Cardinality and participation
Cardinality states the maximum number of instances that can take part in a relationship on each side, while participation, also called optionality or modality, states the minimum: whether an instance must take part at all. Together they encode business rules such as “every order belongs to exactly one customer, and a customer may have no orders yet”.
| Relationship type | Example | Relational implementation |
|---|---|---|
| One-to-one | Employee and parking permit | Foreign key with a unique constraint |
| One-to-many | Customer and orders | Foreign key on the many side |
| Many-to-many | Students and courses | Junction table with two foreign keys |
| Recursive | Employee manages employee | Foreign key referencing the same table |
| Ternary | Supplier supplies part to project | Table with three foreign keys |
A many-to-many relationship cannot be stored directly in a relational table. It is resolved into an associative entity, such as Enrollment between Student and Course, which often gains attributes of its own, for example a grade or an enrollment date. Spotting these hidden entities is one of the main benefits of drawing the diagram.
ER diagram notations
Different ER diagram notations express the same concepts with different symbols, and teams should pick one and use it consistently. The choice usually follows the tooling and the audience.
| Notation | How relationships are shown | Strengths | Typical setting |
|---|---|---|---|
| Chen | Diamonds with cardinality labels | Precise conceptual modeling, attributes as ovals | Teaching, conceptual design |
| Crow’s foot | Line ends with bars, circles and three-prong forks | Compact, readable for large schemas | Industry tools and database IDEs |
| UML class diagram | Associations with multiplicities such as 1..* | Shared language with software design | Object-oriented projects |
| IDEF1X | Solid and dashed lines with dots | Formal identifying versus non-identifying distinction | Government and large enterprise standards |
| Barker | Solid and dashed half-lines with forks | Clear optionality | Oracle-centric modeling |
In crow’s foot notation, the symbol closest to the entity gives the maximum and the inner symbol gives the minimum. A bar means one, a circle means zero and the fork means many, so circle plus fork reads as zero or many, and two bars read as exactly one.
Conceptual, logical and physical ER models
ER diagrams are drawn at three levels of abstraction, each for a different audience. Moving from one level to the next adds detail and commits the design to more technical decisions.
| Level | Contents | Audience | Typical decisions |
|---|---|---|---|
| Conceptual | Main entities and relationships, few attributes | Business stakeholders | Scope and vocabulary |
| Logical | All attributes, keys, resolved many-to-many relationships | Analysts and architects | Normalization, identifiers |
| Physical | Tables, columns, data types, indexes, constraints | Developers and administrators | Engine-specific types, performance |
Normalization usually happens between the logical and physical levels, checking that each fact is stored once. Analytical systems may deliberately depart from it, for example with a star schema, which is still drawn as an ER diagram but with fact and dimension tables in place of a normalized structure.
Common mistakes in ER modeling
- Confusing entities with attributes: modeling a city as text in several tables instead of one City entity creates spelling variants and update anomalies.
- Leaving many-to-many relationships unresolved at the logical level, which hides an associative entity and its attributes.
- Wrong participation constraints: marking a relationship as mandatory when the business allows exceptions forces placeholder records.
- Fan traps and chasm traps: relationship paths that seem to connect entities but return wrong or missing combinations when queried.
- Diagram drift: a diagram that is not regenerated or reviewed after schema migrations quickly becomes misleading documentation.
The best safeguard is to validate the diagram with sample data and with the people who know the business rules, reading each relationship aloud as a sentence in both directions.
Stripped to the essentials
An ER diagram is a map of the information a system needs to keep. Boxes stand for things such as customers, products and orders. Lines between the boxes show how they connect, and small symbols at the ends say how many of one thing can belong to another.
It works like an architect’s floor plan: everyone can look at it, point out a missing room and agree on the layout before the expensive building work starts.
Where it is applied
A team building scheduling software for a network of 12 medical clinics starts with a conceptual ER diagram of Patient, Doctor, Clinic and Appointment. Reviewing it with clinic managers reveals that one doctor works at several clinics, so the original one-to-many link between Doctor and Clinic is wrong. The team changes it to a many-to-many relationship resolved by a Shift entity with date, start time and room.
The logical model ends with 9 entities, and the physical model adds data types, foreign keys and a unique constraint that prevents double booking of a room. Because the error was found on the diagram rather than in production, no data migration was ever needed for that rule, and the diagram later served new developers as their first map of the schema.