Entity-Relationship (ER) Modelling

Entity-Relationship (ER) modelling is a technique for designing a database's structure before writing any SQL, by identifying the real-world "things" a system needs to track, the facts about each thing, and how they relate to one another. An ER diagram is the visual expression of this model, and it typically becomes the direct blueprint for a set of CREATE TABLE statements.

Why it matters

Before writing any SQL you have to decide which tables exist and how they connect. ER modelling is that step: naming the things the system deals with and the relationships between them. A mistake here, such as treating a many-to-many relationship as one-to-many, means rebuilding tables later, when they are full of data.

Entities and Attributes

An entity is a distinct real-world object or concept the database needs to store data about — for example, Customer, Product, or Order. An attribute is a property of an entity, such as a Customer's name and email, or a Product's price. In the relational model, an entity typically becomes a table, and its attributes become that table's columns.

Relationships

A relationship describes how two entities are associated — for example, a Customer "places" an Order, or a Student "enrolls in" a Course. Relationships are what foreign keys implement physically once the model is turned into tables.

Cardinality: One-to-Many and Many-to-Many

Cardinality describes how many instances of one entity can relate to how many instances of another.

• One-to-many (1:N) — one Customer can place many Orders, but each Order belongs to exactly one Customer. Implemented with a foreign key on the "many" side (orders.customer_id).
• Many-to-many (M:N) — one Student can enroll in many Courses, and one Course can have many Students. This cannot be represented with a single foreign key; it requires a separate join (junction) table, such as enrollments(student_id, course_id), with a composite key.
• One-to-one (1:1) — one entity instance relates to exactly one instance of another, such as one Employee having exactly one dedicated ParkingSpot; less common, usually implemented with a unique foreign key.

Example

-- One-to-many: one customer -> many orders
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id)
);

-- Many-to-many: students <-> courses, via a junction table
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title VARCHAR(120) NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

Five tables created; enrollments resolves the many-to-many relationship between students and courses.

Exercise

A student can take many courses and a course has many students. Model this with a junction table enrollments whose primary key is the pair of ids, each a foreign key. Then, using the data provided, list the titles of the courses Asha takes, in alphabetical order.

Expected output: title, HTML, Python (one per line)

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT);

-- TODO: create enrollments (student_id, course_id) with a composite primary key

INSERT INTO students (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO courses (id, title) VALUES (1, 'Python'), (2, 'HTML'), (3, 'SQL');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 3);

-- TODO: the titles of Asha's courses, in alphabetical order

CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

INSERT INTO students (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO courses (id, title) VALUES (1, 'Python'), (2, 'HTML'), (3, 'SQL');
INSERT INTO enrollments (student_id, course_id) VALUES (1, 1), (1, 2), (2, 3);

SELECT c.title
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id
WHERE s.name = 'Asha'
ORDER BY c.title;

Quick quiz

Interview questions