Schemas, Keys and Normalisation

Bad schema design causes duplicate data, contradictory records and painful code. A little planning prevents years of cleanup.

Constraints

Constraints let the database enforce rules so bad data never enters. Prefer them over application-only checks.

CREATE TABLE enrolments (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    student_id  INT NOT NULL,
    course_id   INT NOT NULL,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Normalisation

First normal form: no repeating groups. Second: every non-key column depends on the whole key. Third: no column depends on another non-key column. In practice, store one fact in one place.

When to Denormalise

Reporting systems sometimes copy values to avoid expensive joins. Do this deliberately, after measuring, and keep a plan for keeping copies in sync.

Practice

1. Add a CHECK constraint that keeps scores between 0 and 100.
2. Explain why a student can enrol in a course only once.
3. Split a table that stores a list of phone numbers in one column.

Hands-on exercise

Create an enrolments table with:
1. id primary key.
2. student_id and course_id, both NOT NULL.
3. Foreign keys to students and courses.
4. A UNIQUE constraint on the pair.
5. A grade column that only accepts A to F.

Recap

Keys connect tables, constraints protect correctness and normalisation keeps each fact in one place.