Entity-Relationship Diagram
The entity-relationship diagram (ERD) is the graphical form of a data model. It shows the entities about which an organisation keeps information, the attributes that describe them, the identifier that tells each occurrence apart and the relationships that link them, together with the minimum and maximum number of occurrences allowed at each end of a relationship, what is called the cardinality. It is one of the two notations that data modelling uses, alongside the UML class diagram, and the one reached for when designing the schema of a relational database. At the conceptual level the ERD stays in the language of the business and is a shared picture between domain experts and those who implement; at the logical and physical levels it becomes the blueprint of the database. Its cardinality is most often drawn in crow's foot notation.
Goal
The entity-relationship diagram gives a neutral, technology-independent picture of the information an organisation needs and of how that information hangs together: the things the business keeps track of (the entities), what is recorded about each (the attributes) and the rules that link them (the relationships and their cardinalities). It answers a question the prose of a requirements document leaves vague: which entities are we talking about, which depends on which and how many occurrences of one attach to a single occurrence of the other.
Its value shows at two moments. At the conceptual level it is an elicitation and communication artifact: a shared vocabulary between the domain experts, who confirm or reject the entities and relationships, and those who will build the solution. At the logical and physical levels the same model becomes the starting point of a relational schema, the structure of the persistent data the database will implement. The deliverable is the diagram together with the definitions that document it, those of the entities and the attributes, most often held in a data dictionary.
Usage
When to use it
- Structured, persistent data at the heart of the change: records, transactions or master data to frame.
- Data vocabulary missing or contested: fix the entities and their relationships so everyone speaks of the same things.
- Designing a relational database: set the persistent schema up front, the logical and physical ERD carries it through to the database.
- Data migration or integration: reconstruct the real structure of an existing system before any decision to reuse it.
- A counting business rule: cardinality makes the how-many-X-per-Y explicit and open to discussion.
When not to use it
- The subject is behaviour or flow, not data: model the sequence of activities, reach for a process model such as BPMN or a use case.
- The object model must carry operations (behaviour attached to the data): reach for the UML class diagram, which alone expresses them.
- A strictly business audience needs meaning, not structure: fix the vocabulary with a concept model, which that audience reads, where the ERD imposes an IT structure on it.
Description
The elements of the drawing
An ERD reads from a small number of shapes, and a business analyst meets more of them than they build. Being able to read one is enough to validate a model put in front of you.
- The entity, a rectangle named with a singular business noun (Patient, Prescription, Invoice): something physical, organisational, abstract or an event, of which the organisation keeps track.
- The attribute, a property recorded about the entity, listed under its name: the patient's name, the date of a session, the amount of an invoice.
- The unique identifier, the attribute or attributes that tell each occurrence apart from the others. In relational terms it is the primary key; it heads the entity's attributes.
- The relationship, a named line between two entities, read as a verb phrase in both directions: a patient receives sessions, a session concerns a patient.
- The cardinality, carried by the symbol at each end of the line. It is the element that carries the meaning and the one a hurried reader wrongly skips.
Reading cardinality in crow's foot
Crow's foot notation encodes cardinality with a two-part symbol at each end of the relationship. The mark touching the entity gives the maximum: a bar for one, the crow's foot, that three-pronged fork whose tips splay against the entity box, for many. The mark set back along the line gives the minimum: a circle for zero (an optional relationship), a bar for one (a mandatory relationship). Reading along the line toward the entity, you therefore read the minimum then the maximum, and the four combinations read: circle then fork for zero-or-many, bar then fork for one-or-many, two bars for exactly-one, circle then bar for zero-or-one.
A relationship reads in both directions, and each direction reads on the symbol at the opposite end. To learn how many sessions attach to a patient, you read the symbol on the session side; to learn how many patients attach to a session, you read the symbol on the patient side. The counting rule on each side is a business fact: to say that a session belongs to exactly one prescription and that a prescription authorises one to many sessions is to state two rules the business must confirm, each on its own side.
What the drawing does not show
The first pitfall is cardinality itself. Setting a bound without validating it in both directions produces a model that looks complete and lies: a maximum of "many" set by default where the business admits only one case freezes a rule no one confirmed. The second is mixing the levels: letting foreign keys, column types or indexes into a conceptual data model makes it unreadable to the business, exactly the limitation BABOK notes, and freezes a physical design far too early. The third is the attribute filed in the wrong entity or the entity with no identifier of its own, from which a model's classic inconsistencies flow. The fourth is the many-to-many relationship left as it stands: it almost always hides an intermediate entity, called associative, that carries its own attributes and that no one has named. The last is naming drift, entities in the plural, entities that are really processes, unnamed relationships; an ERD's names come from the business vocabulary.
One last point is reading vocabulary more than construction: you sometimes meet subtype and supertype structures or weak entities. A reader recognises them without having to master their construction to validate a model.
The notation is not universal
The same model is drawn in several notations, and confusing them is a pitfall in itself. Chen's notation, that of the founding paper, draws relationships as diamonds. Crow's foot notation, due to Everest and then popularised by Barker's method and the Oracle workshops, is the one BABOK's figure uses and the one actually drawn on a whiteboard. The UML class diagram, finally, carries the same information in a third form, and its vocabulary changes: where the ERD speaks of relationship and cardinality, the class speaks of association and multiplicity, for the same idea of a minimum and maximum bound. The practical rule fits in a line: read the legend, do not mix two notations on one drawing.
That last confusion covers a deeper one, between the ERD and the UML class diagram. The ERD models data: entities, their attributes, their relationships. The class diagram adds behaviour, the operations a class performs, issuing an invoice, opening an account. A domain where what you do with the data matters as much as its structure belongs to the class diagram; a database schema belongs to the ERD. The business analyst works most often at the conceptual level, sometimes the logical one, and leaves the physical level to the database administrator.
AI considerations
A language model renders several concrete services on this technique. It drafts a first ERD from a data dictionary or a requirements text, proposes missing entities and attributes, generates the data definition language (DDL) of a conceptual data model and, the other way round, reverse-engineers an ERD from the schema of an existing database. It also translates from one notation to another, from Chen to crow's foot or to UML. On a legacy system with no documentation the time saved is real, and the draft obtained is corrected faster than a blank page is filled.
The limit is clear and it is a business one: cardinalities cannot be guessed. A machine that reads a schema infers what the database holds, including the structural errors it has accumulated, but it cannot confirm that a prescription covers a single patient, because the answer lies in how the practice works. The minimum and maximum on each side of a relationship are facts that only the domain experts validate. Also out of automation's reach are the scope decisions, what the model keeps and what it leaves out, and the classification of data sensitivity: an ERD of a medical practice carries an AVS number and health data, whose handling engages data protection well beyond what a model can judge on its own.
Examples
A diagram reads best on a case. The concept it makes visible is reading a relationship in both directions: one line carries two counting rules, one per end, and each is read on the symbol at the opposite end. The domain chosen is a physiotherapy practice in French-speaking Switzerland, with four entities, the patient, the prescription, the session and the physiotherapist.
The relationship between the prescription and the session carries the two rules to read. On the session side, the symbol is a crow's foot preceded by a bar: a prescription authorises one to many sessions, never zero, which says a prescription with no session has no reason to be recorded. On the prescription side, the symbol is a double bar: each session belongs to exactly one prescription, neither zero nor two. The two other relationships read the same way. A patient receives zero to many sessions and each session concerns exactly one patient; a physiotherapist delivers zero to many sessions and each session is delivered by exactly one physiotherapist. The session is the point where three relationships meet, and each of its exactly-one ends says the session cannot exist without its patient, its prescription and its physiotherapist.
Visualizations
The shape carries the meaning, which lets you validate a model without reading it line by line. The silhouette gives the type, a named rectangle for an entity, the identifier heading its attributes. The named line gives the relationship, and its two-mark symbol gives the counting rule, the maximum touching the entity, the minimum mark set back along the line. A reviewer who knows crow's foot scans a whole schema by eye and spots a doubtful cardinality without opening a single definition, which is what makes the notation a shared language.
Cost
| Phase | Level | Justification |
|---|---|---|
| Preparation | Low to medium | Gather the business vocabulary, a data dictionary or a concept model if one exists, and identify who can authoritatively confirm the entities and relationships of each domain. |
| Execution | Medium | The drawing itself is quick once the entities are known. The cost lies in validating the cardinalities with the domain experts, which iterates, one counting rule at a time. |
| Documentation | Medium to high | The model and the definitions of its entities and attributes, kept with the data dictionary, must stay current as the data changes, on pain of becoming an archive picture no one consults any more. |
Tooling
A whiteboard or a sheet of paper is enough for the first conceptual sketch: a few entities and their relationships are laid down and corrected standing up, with the business. General diagramming tools, diagrams.net, Lucidchart or Visio, carry entity-relationship stencils and give a clean drawing, but they stop at the drawing. Data-modelling workbenches and computer-aided software engineering tools go further: Sparx Enterprise Architect, erwin Data Modeler, ER/Studio, MySQL Workbench, Oracle SQL Developer Data Modeler or dbdiagram.io generate the database schema from the model and reverse-engineer it from an existing database. The division of roles is simple: the diagramming tool is enough while the ERD is a discussion aid at the conceptual level, the modelling workbench becomes necessary as soon as the model must stay in sync with a real database.
Sources
- IIBA, A Guide to the Business Analysis Body of Knowledge (BABOK Guide) v3, §10.15 Data Modelling: the entity-relationship diagram as one of the two notations of a data model, the list of its elements, cardinality and its reading in both directions and the stated strengths and limitations of its use.
- Peter Chen, "The Entity-Relationship Model: Toward a Unified View of Data", ACM Transactions on Database Systems, vol. 1, no. 1 (1976), pp. 9-36: the founding paper of the entity-relationship model and its base concepts.
- Gordon C. Everest, "Basic Data Structure Models Explained with a Common Example", Proc. Fifth Texas Conference on Computing Systems (1976): the origin of crow's foot cardinality notation.
- Richard Barker, CASE*Method: Entity Relationship Modelling, Addison-Wesley (1990), ISBN 978-0201416961: the reference for the Barker/Oracle variant of crow's foot that a business analyst meets in Oracle and CASE toolchains.