Chapter 4 · Design and Code
Data Modelling with Entity–Relationship Diagrams
- Page 12 of 18
- 16 min read
Almost every program keeps data, and the shape of that data outlives most of the code around it. Screens get redesigned and functions get rewritten, but once real bookings are stored in ClinicQueue's database, changing what a "booking" is becomes slow and risky work. So it pays to think about the data before you create the first table.
On the previous page, Software Design Basics: From Requirements to Modules, you split ClinicQueue into layers, with a data layer at the bottom. This page designs what that layer stores. You will learn a simple drawing technique, the entity–relationship (ER) diagram, draw one for ClinicQueue, and turn it into tables. Every data value in the examples is invented.
What you will learn
- What entities, attributes and relationships are, and how primary and foreign keys connect them.
- The three kinds of cardinality (one-to-one, one-to-many, many-to-many) and why many-to-many needs a link table.
- How to draw and read a plain-text ER diagram for ClinicQueue (Patient, Doctor, Appointment, Reminder).
- Simple rules for turning an ER diagram into database tables.
- The modelling mistakes that cause trouble later, and how to spot them early.
Entities, attributes and relationships
The ER model was introduced by Peter Chen in 1976, in a paper that presented "a special diagrammatic technique" as a tool for database design. Its idea is simple: describe the world your software cares about as things, the facts about those things, and the connections between them, before you think about any particular database.
Entities and attributes
An entity is a kind of thing the system must remember, usually a noun from the requirements: Patient, Doctor, Appointment. Each individual patient is one instance of the Patient entity (later, one row in a table). An attribute is a fact about an entity: a patient's name, phone number and date of birth.
A quick way to find candidates is to underline the nouns in the requirements and user stories you wrote in chapter 2. Take the story "As a patient, I want an SMS reminder the day before my appointment, so that I do not miss it." The nouns are patient, SMS reminder, day and appointment. Patient, Reminder and Appointment become entities; "the day before" becomes an attribute of Reminder (when to send it). Not every noun becomes an entity. The test: does the system need to store several facts about it, and tell one instance from another? If yes, it is probably an entity; if it is a single value, it is an attribute.
Relationships
A relationship is a meaningful connection between entities, usually a verb: a patient books an appointment, a doctor sees an appointment, an appointment gets reminders. Writing each relationship as a full sentence in both directions is the best check that you understand it:
Example:
Patient books Appointment: one patient can book many appointments;
each appointment is booked by exactly one patient.
Doctor sees Appointment: one doctor has many appointments;
each appointment is with exactly one doctor.
Appointment gets Reminder: one appointment can get several reminders (a retry, a change);
each reminder belongs to exactly one appointment.If you cannot finish one of these sentences, you have found a question for Nadia or Rina, not a detail to guess.
Keys: primary and foreign
A primary key is an attribute (or a set of attributes) whose value is unique for every instance and never empty. It is how the system tells two patients called "Ayesha Khan" apart. A foreign key is an attribute in one entity that holds the primary key of another, and so records the relationship: an Appointment holds patient_id and doctor_id.
Which value should be the primary key? Teams choose between two kinds:
| Kind | Example | For | Against |
|---|---|---|---|
| Natural key: a real-world value | the patient's phone number | Meaningful; no extra column | Real values change and are not always unique: phone numbers change, and families share one |
| Surrogate key: a number or code the system invents | id = 1, 2, 3 … | Never changes, always unique, short | Means nothing to humans; you still need rules for real duplicates |
ClinicQueue uses surrogate keys (id) for its entities. That is a common default, not a law: a short code that truly never changes, such as a standard two-letter language code, works well as a natural key.
Cardinality: how many on each side
Cardinality says how many instances of one entity can be connected to one instance of the other. There are three basic kinds.
| Cardinality | ClinicQueue example | How it is stored |
|---|---|---|
| One-to-one (1:1) | A doctor has at most one staff login account, and an account belongs to at most one doctor. | A foreign key on either side, marked unique. |
| One-to-many (1:N) | A patient books many appointments; each appointment has one patient. | A foreign key on the "many" side (appointment.patient_id). |
| Many-to-many (M:N) | A doctor speaks several languages; each language is spoken by several doctors. | A separate link table holding two foreign keys. |
Why many-to-many needs a link table
Nadia wants patients to choose a doctor who speaks their language (a story planned for version 2). Dr. Rahman speaks Bangla and English; the clinic's second doctor, Dr. Mehta, speaks Bangla and Hindi. Where do you put this? One language column on the doctor cannot hold two values cleanly, and a doctor_id column on the language cannot either. The answer is a third table, a link table (also called a junction or association table), with one row per pair:
Example:
doctor doctor_language language
+----+------------+ +-----------+----------+ +------+---------+
| id | name | | doctor_id | language | | code | name |
+----+------------+ +-----------+----------+ +------+---------+
| 1 | Dr. Rahman | | 1 | bn | | bn | Bangla |
| 2 | Dr. Mehta | | 1 | en | | en | English |
+----+------------+ | 2 | bn | | hi | Hindi |
| 2 | hi | +------+---------+
+-----------+----------+"Which doctors speak Bangla?" and "Which languages does Dr. Rahman speak?" are now both simple look-ups. A many-to-many relationship always becomes two one-to-many relationships through the link table.
Optional or mandatory
Cardinality also has a minimum. Every appointment must have a patient (minimum one), but a patient may have no appointments yet, for example right after registering (minimum zero). This matters for the code: "appointment without a patient" must be impossible, while "patient without appointments" is normal and the screens must handle it.
One-to-one: split or merge?
A one-to-one relationship often means two entities could be one table. Keep them separate when the second part is optional, sensitive, or owned by a different module. The staff login holds a password hash and belongs to the security part of the system, so ClinicQueue keeps it apart from the doctor's public profile. If neither reason applies, merging is simpler.
An ER diagram for ClinicQueue
There are several drawing styles. Chen's own notation uses rectangles for entities and diamonds for relationships; many teams today use "crow's-foot" lines, a later convention. On paper or a whiteboard any style works if the team agrees on it. In plain text, writing 1 and N at the ends of each line is clear enough:
Example:
+---------------+ 1 N +-----------------+ N 1 +---------------+
| Patient |------------| Appointment |------------| Doctor |
|---------------| books |-----------------| with |---------------|
| PK id | | PK id | | PK id |
| name | | FK patient_id | | name |
| phone | | FK doctor_id | | specialty |
| birth_date | | starts_at | +---------------+
+---------------+ | status |
+-----------------+
| 1
| gets
| N
+--------------------+
| Reminder |
|--------------------|
| PK id |
| FK appointment_id |
| send_at |
| text |
| sent_status |
+--------------------+
PK = primary key, FK = foreign key, 1 / N = one / manyReading the diagram
- Patient and Doctor are many-to-many, through Appointment. A patient may see several doctors over time, and a doctor sees many patients. Here the link is not a bare link table: an appointment has its own facts (time, status) and its own reminders, so it is a full entity. This is very common: many link tables grow into real entities.
- Reminder is separate from Appointment. Farhan first suggested a single
reminder_sentcolumn on Appointment. Kwame asked what happens when the first SMS fails and the system retries, or when the appointment moves and a new reminder goes out. One column cannot record that history; a Reminder entity can. The text is stored too, because the AI feature will draft it and the team must be able to see what each patient actually received. - Status values are a fixed list. Appointment
statusis one of booked, cancelled, arrived or done, agreed with Rina. A fixed list prevents "Cancelled", "cancel" and "canceled" appearing side by side.
Where the diagram meets the requirements
On Functional and Non-functional Requirements, and Acceptance Criteria you wrote a criterion like "a slot that is already taken cannot be booked again". The ER model is where that becomes a rule the database enforces: no two appointments may have the same doctor_id and starts_at. A rule enforced in the data is safer than one that only the booking screen checks, because every future screen and script must obey it too.
From ER diagram to tables
Turning the diagram into tables follows a few mechanical rules:
| In the ER diagram | In the database |
|---|---|
| Entity | A table (one row per instance) |
| Attribute | A column with a data type (text, integer, date, date and time) |
| Primary key | A column (or set of columns) declared unique and not empty |
| One-to-many | A foreign key column on the "many" side |
| One-to-one | A foreign key on one side, also declared unique |
| Many-to-many | A link table with two foreign keys; together they form its primary key |
| Mandatory relationship | The foreign key column may not be empty |
Applied to ClinicQueue, Farhan writes this table plan before any code:
Example:
patient id PK | name | phone | birth_date (date)
doctor id PK | name | specialty
language code PK | name
doctor_language doctor_id FK -> doctor.id | language FK -> language.code
PK (doctor_id, language)
appointment id PK | patient_id FK -> patient.id, not empty
| doctor_id FK -> doctor.id, not empty
| starts_at (date and time, clinic time zone)
| status (booked/cancelled/arrived/done)
UNIQUE (doctor_id, starts_at) -- one booking per doctor per slot
reminder id PK | appointment_id FK -> appointment.id, not empty
| send_at | text | sent_status (pending/sent/failed)Notice the small decisions written down: the time zone of starts_at, the allowed status values, and which foreign keys may not be empty. Each one is cheap to decide now and expensive to discover later in live data.
Writing these tables in SQL, adding constraints and joining them back together is taught in SQL and Data Handling for AI: Beginner. This page stays at the design level, which is the same whatever database you use.
Model first or code first?
Practitioners differ on how much to model before coding. Some draw the whole data model up front, because the database is hard to change once it holds real data. Others model only what the next release needs and change the schema step by step with small, tested changes, because up-front models often include tables nobody uses (the YAGNI rule of thumb from the previous page). For ClinicQueue a middle path works: model the entities the first release needs (the four above), plus the two language tables because the version 2 doctor choice is already agreed, check them with Nadia and Rina, and add new entities when a feature needs them.
Modelling mistakes to look for
These are the problems a reviewer looks for in an ER diagram. Each one is easy to fix on paper and painful to fix once data exists.
| Mistake | ClinicQueue example | Better |
|---|---|---|
| Repeating columns | reminder_1, reminder_2, reminder_3 on Appointment | A separate Reminder entity, one row per reminder |
| Lists inside one value | Doctor languages = "bn, en" | A link table, one row per pair |
| Copying instead of referencing | The doctor's name typed into every appointment row | A doctor_id foreign key; the name lives in one place |
| Storing values you can compute | A patient's age column, wrong a year later | Store birth_date; compute the age when needed |
| A changeable value as the key | Phone number as the patient's primary key | A surrogate id; phone is an ordinary attribute |
| Modelling screens, not things | A "QueueScreen" entity | The queue is today's appointments, sorted: a query, not a table |
| Many-to-many drawn as one-to-many | doctor_id on Patient ("the patient's doctor") | Check both sentences; patients see several doctors, so go through Appointment |
One honest exception to "copying instead of referencing": sometimes you must keep a copy. The reminder text that was sent stays as it was, even if the template changes later, because it records what really happened. Copying is a mistake when the copy is supposed to stay equal to the original; it is correct when it records a moment in time.
Where this shows up at work
- Before a new feature that stores data, teams sketch the new entities and relationships and review them, often in a short design meeting.
- When joining a project, reading the ER diagram (or drawing one from the existing tables) is one of the fastest ways to understand the system.
- Freelancers include a data model in a proposal so the client can confirm the vocabulary: "Is a 'visit' the same thing as an 'appointment'?"
- Reporting and AI features depend on clean data: a model with clear keys and no copied values makes those features much easier to build.
Common mistakes
- Modelling alone, from assumptions. Farhan assumed one phone number means one patient; Rina knows that a mother often books for three children with her phone. Fix: read the relationship sentences aloud to the people who do the work and ask "is this always true?"
- Skipping the minimum cardinality. If nobody decides whether an appointment may exist without a doctor, the database will allow it and the screens will break on it. Fix: for every relationship, write "must" or "may" on both sides.
- Modelling features nobody asked for. Tables for insurance, invoices and lab results "just in case" add work and confusion. Fix: model what the next release needs; keep a note of likely future entities.
- Leaving the diagram behind. The diagram is drawn once, the tables change, and soon the diagram misleads. Fix: update it with each schema change, or generate it from the database.
Try it yourself
- Easy: Give the cardinality (1:1, 1:N or M:N) of each pair and say how it would be stored: (a) Appointment and Reminder; (b) Patient and Doctor; (c) Doctor and staff login account; (d) Doctor and Language.
- Medium: Nadia adds services: general consultation, vaccination, follow-up. Each doctor offers some services, and each service is offered by some doctors. Every appointment is for exactly one service, and each service has a standard length in minutes. Extend the model: which new entities, relationships and keys do you need? Write the new tables in the same style as Farhan's table plan.
- Hard: A reviewer finds this design for Patient:
phone(primary key),name,age,children= "Rafi, Mim",last_doctor_name. List every problem you can find, name the mistake from this page for each, and propose a better design.
Answers
- (a) 1:N: one appointment can get several reminders; foreign key
appointment_idon Reminder. (b) M:N: a patient sees several doctors and a doctor sees many patients; stored through Appointment, which acts as the link and has its own attributes. (c) 1:1: a foreign key on one side declared unique (for exampledoctor_idon the account table), kept separate because the login is sensitive. (d) M:N: the link tabledoctor_languagewith two foreign keys. - Add an entity Service (
idPK,name,length_minutes). Doctor–Service is M:N, so add a link table doctor_service (doctor_idFK,service_idFK, primary key both together). Service–Appointment is 1:N (each appointment is for exactly one service, a service has many appointments), so addservice_idFK, not empty, to Appointment. Table plan:service id PK | name | length_minutes;doctor_service doctor_id FK -> doctor.id | service_id FK -> service.id, PK (doctor_id, service_id);appointment ... | service_id FK -> service.id, not empty. A good answer also notices a rule the database alone cannot easily check: the appointment's service should be one the doctor offers, so the booking logic must check it. Bonus: the slot length now comes from the service, which affects the "slot already taken" rule. - Problems: (1) phone as primary key: a changeable, shared value as the key; families share a phone and numbers change. (2)
age: a stored value you can compute, wrong after a birthday. (3)children= "Rafi, Mim": a list inside one value, and the children are patients too. (4)last_doctor_name: copying instead of referencing, and it can be computed from the latest appointment. Better design: Patient (idPK,name,phone,birth_date); children become Patient rows of their own; to know who books for whom, add a relationship such asguardian_idFK -> patient.id on the child (1:N, optional), or a small link table if a child can have two guardians. The last doctor is found from the patient's most recent appointment. A good answer names all four problems, gives a reason for each, and keeps phone as an ordinary attribute that need not be unique.
Summary
- An ER model (Peter Chen, 1976) describes data as entities, their attributes, and the relationships between them.
- Primary keys identify each instance; foreign keys record relationships. Surrogate keys are a common default because real-world values change.
- Cardinality is one-to-one, one-to-many or many-to-many, with a minimum of zero or one; many-to-many needs a link table, which often grows into a real entity such as Appointment.
- Mapping to tables is mechanical: entity → table, 1:N → foreign key on the many side, M:N → link table.
- Look for repeating columns, lists in one value, copied data, stored computed values, changeable keys and entities that are really screens.
Next: Writing Maintainable Code: how to write the code on top of this data so that the next person, or you in six months, can read it and change it safely.

