Data Modeling & ERDs

Drawing an ERD

18 min Lesson 4 of 10

Drawing an ERD

An Entity-Relationship Diagram (ERD) is the primary tool analysts use to visualise the data a system must store and the rules that govern how that data is connected. Where a requirements document describes what the system should do, an ERD describes what information the system must remember. In this lesson you will learn the standard crow-foot notation, read a complete worked example for a clinic booking domain, and walk away with a repeatable process for drawing ERDs from scratch.

The Crow-Foot Notation at a Glance

Crow-foot ERDs represent four concepts with four visual symbols:

Entities — plain rectangles. Each row in the resulting database table is one instance of the entity (e.g., one specific patient, one specific appointment).
Attributes — listed inside the entity box. Primary keys are marked PK; foreign keys are marked FK.
Relationships — lines connecting entity boxes, labelled with a verb phrase (has, places, assigns).
Cardinality symbols — drawn at each end of a relationship line using the crow-foot markers shown below.

Crow-foot symbols encode two pieces of information at each end of a line: optionality (must this instance participate?) and maximum (can many instances participate?).

The four crow-foot cardinality symbols: exactly one, zero-or-one, one-or-many, and zero-or-many.

Reading a crow-foot line: stand at one entity and walk toward the other. The symbol you reach first (closest to the far entity) tells you the maximum (crow-foot = many; single bar = one). The symbol second (further from the far entity, closer to the line) tells you the minimum (circle = zero / optional; bar = one / mandatory).

A Complete Worked Example: Clinic Booking System

Consider a private clinic that needs to track patients, doctors, appointments, and the treatments prescribed during each appointment. The business rules gathered during requirements interviews are:

A Patient may book zero or many Appointments. Every Appointment must belong to exactly one Patient.
A Doctor may conduct one or many Appointments. Every Appointment is conducted by exactly one Doctor.
During an Appointment, a Doctor may prescribe zero or many Treatments. Each Treatment is prescribed in exactly one Appointment.
Every Treatment references exactly one Drug (from the formulary). A Drug may appear in zero or many Treatments across all appointments.

These four rules translate directly into the ERD below. Study each entity box (name and key attributes), each relationship line (verb label), and each pair of crow-foot symbols at the line ends.

Crow-foot ERD for a clinic booking system: Patient, Doctor, Appointment, Treatment, and Drug with full cardinality notation.

Walking Through the Diagram

Let us read each relationship aloud using the rule "one [entity] [verb] zero-or-many [entity]":

Patient books Appointments: one Patient books zero-or-many Appointments (a new patient may not yet have booked any); each Appointment is booked by exactly one Patient — the double bar at the Patient end means mandatory, one only.
Doctor conducts Appointments: one Doctor conducts zero-or-many Appointments; each Appointment is conducted by exactly one Doctor. The circle at the Appointment end nearest the Doctor confirms that zero appointments is valid for a newly hired doctor.
Appointment prescribes Treatments: one Appointment may produce zero-or-many Treatments (a visit for a check-up may result in no prescription); each Treatment belongs to exactly one Appointment.
Treatment references Drug: many Treatments (across many appointments) may reference a Drug (zero-or-many); each Treatment references exactly one Drug.

Foreign keys follow the relationship: notice that the "many" side entity always holds the foreign key. APPOINTMENT holds patient_id and doctor_id (the FK goes to the "one" side's PK). TREATMENT holds appointment_id and drug_id. This is a universal rule — the FK always lives on the many side.

Step-by-Step Process for Drawing an ERD

Follow these five steps every time you model a new domain:

List the entities. Scan the requirements for persistent nouns — things the system must remember between sessions. Eliminate adjectives, verbs, and duplicate synonyms. The clinic gives us: Patient, Doctor, Appointment, Treatment, Drug.
Identify attributes and primary keys. For each entity, ask "what data describes one instance?" Add a surrogate or natural primary key (e.g., patient_id, drug_id).
Identify relationships. Scan the requirements for verb phrases linking two entities: "a doctor conducts appointments", "a prescription references a drug". Name each relationship with a short, active-voice verb.
Assign cardinality. For each relationship, ask the two mandatory questions: (a) can one instance of entity A relate to many instances of entity B? (b) is that relationship mandatory or optional? Answer both for each direction, then draw the correct crow-foot symbol at each end.
Add foreign keys. On the "many" side of every relationship, add the primary key of the "one" side as a foreign key attribute (FK). For many-to-many relationships a junction entity is required (covered in Lesson 6).

Attributes that look like entities: be careful with values such as specialty or status. If the system simply stores a text value and no other data hangs off it, it is an attribute. If the system needs to track extra data about it (e.g., specialty description, specialty code, associated department), model it as a separate entity with a relationship.

Cardinality from Real Business Rules

The decision between zero-or-many and one-or-many, or between exactly-one and zero-or-one, is always a business decision, not a technical one. Ask stakeholders directly:

"Can a doctor exist in the system before they have conducted any appointment?" → Yes → zero-or-many on the Appointment end.
"Must every Treatment record belong to an appointment, or can standalone prescriptions exist?" → Must belong → exactly-one on the Appointment end of the Treatment relationship.
"Is it possible to record a Drug in the formulary before it has been prescribed to anyone?" → Yes → zero-or-many on the Treatment end.

Document the answer to each question in your data dictionary (Lesson 9) alongside the ERD. That paper trail is invaluable when a developer or database administrator queries a cardinality choice six months after the project launched.

Tooling choices: professional analysts use Lucidchart, draw.io, Microsoft Visio, dbdiagram.io, or the ERD view in MySQL Workbench. For a quick whiteboard session, the only materials you need are a marker and the four crow-foot symbols. The thinking process is identical regardless of the tool.

Common Mistakes and How to Avoid Them

Putting the FK on the wrong side. The foreign key always belongs to the many-side entity. If you find yourself putting appointment_id on the Patient row, you have placed it on the one side — flip it.
Confusing optional with zero-cardinality. "Optional" does not mean the relationship does not exist; it means zero instances are currently linked. The entity itself still exists; it just has no matching rows on the other side yet.
Modelling derived attributes. Age is derived from date of birth; do not store both unless there is a specific performance justification. Store the source data; compute the derived value in queries.
Overcrowding one entity. If an entity has 20+ attributes, examine whether it conceals two separate concerns that should be split into two entities with a one-to-one or one-to-many relationship.

A well-drawn ERD communicates both the structure of the data and the business rules that constrain it. When stakeholders sign off on an ERD, they are approving the fundamental data architecture of the system — a decision that is far cheaper to change on paper than in production code.