Designing a database: the ER model

Introduction

Before writing CREATE TABLE, designers draw an entity-relationship (ER) model: what things the database is about, what is known about each, and how they are linked. It is easy to discuss with people who do not know SQL, and it turns into tables by a few fixed rules.

A good database starts on paper. The ER model is how designers agree on what to store before writing any SQL, and it converts into tables by a few fixed rules.

How it works

The parts

Attributes come in kinds: composite (a name made of first and last name), multivalued (a student's several phone numbers, drawn with a double oval) and derived (age, worked out from the date of birth, drawn with a dashed oval).

Cardinality and participation

Cardinality says how many of one entity can link to the other:

• One-to-one (1:1): a department has one head, and a teacher heads at most one department.
• One-to-many (1:N): a department has many teachers; each teacher is in one department.
• Many-to-many (M:N): a student takes many courses; a course has many students.

Participation says whether every entity must take part: every teacher must belong to a department (total participation, a double line), but not every teacher heads one (partial).

A weak entity cannot be identified on its own: a Dependent of an employee (their child, say) is identified by the employee plus the dependent's name. It is drawn with a double rectangle.

 STUDENT ───────< ENROLLS >─────── COURSE
 (student_id,        M     N       (code, title,
  name, dept)     (grade)           credits)

From ER to tables

1. Each entity becomes a table; its key becomes the primary key.
2. A 1:N relationship becomes a foreign key on the N side: teachers.dept_id.
3. A M:N relationship becomes a table of its own, a junction table, holding both keys (together, its primary key) and the relationship's own attributes, such as grade.
4. A 1:1 relationship becomes a foreign key on either side, marked UNIQUE.
5. A multivalued attribute becomes a table of its own: phones(student_id, phone).
6. A weak entity becomes a table whose primary key is the owner's key plus its own partial key.

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  credits INTEGER NOT NULL
);
-- The many-to-many relationship, with its own attribute: grade
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_code TEXT REFERENCES courses(code),
  grade TEXT,
  PRIMARY KEY (student_id, course_code)
);

INSERT INTO students (name) VALUES ('Rahim'), ('Nadia'), ('Karim');
INSERT INTO courses VALUES ('CSE101', 'Programming', 3), ('CSE201', 'Data Structures', 3), ('MAT101', 'Calculus', 4);
INSERT INTO enrollments VALUES
  (1, 'CSE101', 'A'), (1, 'MAT101', 'B'),
  (2, 'CSE101', 'A+'), (2, 'CSE201', 'A'), (2, 'MAT101', 'A'),
  (3, 'CSE201', NULL);

-- Nadia's courses
SELECT c.code, c.title, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.code = e.course_code
WHERE s.name = 'Nadia';

-- Students and total credits
SELECT s.name, COUNT(*) AS courses, SUM(c.credits) AS credits
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.code = e.course_code
GROUP BY s.id, s.name
ORDER BY credits DESC, s.name;

-- Rahim cannot enroll in CSE101 twice: the primary key refuses
INSERT INTO enrollments VALUES (1, 'CSE101', 'B');

The enrollments table is the M:N relationship. Its primary key (student_id, course_code) stops a student from enrolling in the same course twice, and its two foreign keys stop enrollments for students or courses that do not exist. The queries then join the three tables back together.

Syntax

CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_code TEXT REFERENCES courses(code),
  grade TEXT,
  PRIMARY KEY (student_id, course_code)
);

Example

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  credits INTEGER NOT NULL
);
-- The many-to-many relationship becomes a table of its own
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_code TEXT REFERENCES courses(code),
  grade TEXT,
  PRIMARY KEY (student_id, course_code)
);
INSERT INTO students (name) VALUES ('Rahim'), ('Nadia'), ('Karim');
INSERT INTO courses VALUES ('CSE101', 'Programming', 3), ('MAT101', 'Calculus', 4);
INSERT INTO enrollments VALUES (1, 'CSE101', 'A'), (2, 'CSE101', 'A+'), (2, 'MAT101', 'B');

-- How many students take each course
SELECT c.code, c.title, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_code = c.code
GROUP BY c.code, c.title
ORDER BY c.code;

-- Each student's courses, one row per enrollment
SELECT s.name, e.course_code, e.grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
ORDER BY s.name, e.course_code;

This smaller example builds the three tables of the university model and asks two questions through the junction table. Unlike the example above, it finishes without an error, so you can see both results.

Line by line

1. students and courses are the two entities, each with its own key.
2. enrollments is the M:N relationship. It holds both keys, which together are its primary key, and the relationship's own attribute, grade.
3. The first query starts from courses and LEFT JOINs the enrollments, so a course with no students would still appear. COUNT(e.student_id) counts the students per course.
4. CSE101 has 2 students and MAT101 has 1.
5. The second query joins each student to their enrollments. Karim has none, so the inner join leaves him out.

Expected result

code    title        students
------  -----------  --------
CSE101  Programming  2
MAT101  Calculus     1
(2 rows)

name   course_code  grade
-----  -----------  -----
Nadia  CSE101       A+
Nadia  MAT101       B
Rahim  CSE101       A
(3 rows)

In the real world

Students and courses, doctors and patients, products and orders, and actors and films are all many-to-many relationships, and every one is stored with a junction table. ER diagrams are a standard part of the documents written before a system is built.

Common mistakes

• Storing several values in one column, such as a list of course codes, instead of a junction table.
• Putting the foreign key on the wrong side of a 1:N relationship.
• Forgetting the composite primary key on a junction table, which allows duplicate pairs.

Best practices

• List the nouns of the problem description as candidate entities.
• Decide the cardinality of each relationship with real examples.
• Draw the model and check it with the people who know the data.

Summary

ER modelling describes entities, attributes and relationships with their cardinality and participation. Fixed rules turn the model into tables: entities become tables, 1:N relationships become foreign keys, and M:N relationships become junction tables.

Mark lesson complete