Modelling data: entities and relationships

Why model before you build?

In module 1 you met keys and the three kinds of relationship. Now you use them to design a database from scratch. Changing a design on paper costs nothing. Changing it after a year, when the tables hold lakhs of rows and ten programs read them, takes migrations, testing and late nights. A few hours of modelling saves weeks later.

A data model is a description of the things your app must remember and how they connect. It has three parts:

• Entity: a kind of thing you store many of, such as Student, Course or Doctor. Each entity usually becomes a table.
• Attribute: one detail of an entity, such as a student's name or a course's fee. Attributes become columns.
• Relationship: how entities connect, such as "a student enrols in a course". Relationships become foreign keys or junction tables.

From requirements to a model

Start from plain sentences written with the people who will use the app. A simple trick is to underline the nouns and the verbs. Nouns that have their own details are entities; small nouns that only describe something are attributes; verbs are relationships.

Cardinality and optionality

Cardinality is the "how many" of a relationship: one or many on each side. Optionality says whether a link must exist at all: a course may have zero students in its first week, but an enrolment must always point to one student. Ask two questions for each side: "at least how many?" (zero or one) and "at most how many?" (one or many).

Crow's foot notation, in words

The most common way to draw an ER (entity-relationship) diagram is crow's foot notation. Tables are boxes and relationships are lines. The end of each line shows cardinality: a three-pronged fork that looks like a crow's foot means "many", a short bar means "one", and a small circle means "zero", so the link is optional.

teachers ||------o< courses       one teacher teaches zero or many courses
students ||------o< enrolments    one student has zero or many enrolments
courses  ||------o< enrolments    one course has zero or many enrolments
enrolments: student_id FK, course_id FK, batch, joined_on; PK (student_id, course_id)

Many-to-many becomes a junction table

A relational table cannot hold "many" on both sides, so every many-to-many relationship becomes a junction table with two foreign keys. Notice that the relationship often has its own attributes: the batch and the joining date belong to neither the student nor the course, but to the enrolment. When a relationship gathers many attributes, it is really an entity of its own, and that is fine.

SELECT c.code, c.title
FROM enrolments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 3;
-- CS102 | Databases
-- MA101 | Statistics

Choosing keys on purpose

A candidate key is any column, or set of columns, that could identify a row: for a course both id and code are unique. You pick one as the primary key and protect the others with UNIQUE. Most teams use a surrogate id as the primary key and keep natural values such as a course code, a PAN or an email under a UNIQUE rule.

• A junction table can use the pair as its primary key, PRIMARY KEY (student_id, course_id), which also blocks the same student joining the same course twice.
• If the same pair may appear more than once (a patient sees the same doctor many times), the pair is not unique. Give the table its own id, and add the date to make each row distinct.
• A one-to-one relationship is a foreign key with UNIQUE on it; an optional one-to-many relationship is a foreign key that allows NULL.

Common mistakes

• Jumping straight to CREATE TABLE. Fix: write the requirement sentences and a rough ER diagram first, and show them to a real user.
• Putting relationship details on the wrong table, such as a grade column in students. Fix: ask "whose detail is this?"; a grade belongs to one student in one course, so it goes in the junction table.
• Making a junction table's pair the primary key when repeats are real (repeat appointments). Fix: use an id plus the date.
• Modelling a list as columns: course1, course2, course3. Fix: one row per pair in a junction table. Topic 2 explains why.
• Forgetting optionality. Fix: decide NOT NULL or nullable for every foreign key, from the "at least how many?" question.