Designing a database · ⁨数据库设计⁩

One renamed course now has two titles

• A school copies a course title into every enrolment row. Renaming some copies leaves two conflicting titles for the same course.
• A relational database 关系数据库 stores rows in tables 表. First identify the entities 实体, such as students and courses, and the relationships between them.

Separate identity from a changeable label

• A primary key 主键 uniquely identifies a row and cannot be null. A stable student ID lets a name change without changing the student’s identity.
• A natural key can work when its uniqueness and stability are guaranteed. A name is a poor key here because different students can share it; an email may also change.

Represent the many-to-many relationship

• One student can take many courses; one course can have many students. Use an enrolments table with one row for each student–course pair.
• A foreign key 外键 links each enrolment to a valid student or course key. A composite primary key (student_id, course_id) prevents the same pair appearing twice.

Store a course fact with the course

• Normalisation 规范化 organizes tables using their dependencies to reduce inconsistent repetition. In this design, a course ID determines its title, so store the title in courses.
• Repeated foreign-key values are expected: many enrolments can refer to one course. “Never repeat any value” is not a useful database design rule.

Enable and test the constraints

• In SQLite, enable foreign-key enforcement on each connection with PRAGMA foreign_keys = ON before the work begins. Declaring a relationship alone is not enough.
• Test a valid enrolment, a duplicate pair and an enrolment referring to a missing course. Constraints should reject the last two; they do not decide which caller has permission.

PRAGMA foreign_keys = ON;
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id),
  CHECK (student_id IS NOT NULL AND course_id IS NOT NULL)
);

Check the design against a new case

• For library loans, a book title is not necessarily a unique physical copy. Identify the borrower, the copy and the loan event before choosing keys.
• Decide whether repeat events are allowed. A student–course pair can identify one enrolment here, but a borrower–copy pair cannot distinguish repeated loans on different dates.