From Brief to Tables

Read the brief and underline every noun. Course, lesson, learner, quiz, attempt, certificate. Most of those become tables. The verbs between them - a learner enrols on a course, an attempt belongs to a quiz - become foreign keys or join tables.

It is a crude technique and it gets you eighty percent of a schema in twenty minutes. The remaining twenty percent is what this module is actually about.

One fact, one place

The rule behind normalisation, stated without the word: every fact should be stored exactly once.

If a course's title appears in catalog_courses and again in every progress_enrolments row, then renaming a course means finding every copy. Miss one and the database now holds two different answers to the same question, and nothing can tell you which is right.

So: the enrolment stores a course_id, and the title is read from the course. One fact, one place.

Where the rule is broken on purpose

CREATE TABLE catalog_courses (
  id               CHAR(36) NOT NULL,
  title            VARCHAR(200) NOT NULL,
  -- Denormalised; refreshed by catalog_refresh_course_rollup().
  duration_minutes INT NOT NULL DEFAULT 0,
  lesson_count     INT NOT NULL DEFAULT 0,
  ...
);

lesson_count is a fact stored twice: here, and implicitly in the rows of catalog_lessons. That is deliberate. The catalogue page shows a lesson count for every course in a list of fifty, and counting lessons fifty times per page view is a cost paid on every request to save a maintenance problem that happens when a lesson is added.

The thing that makes it safe is that one named procedure owns the refresh. Denormalisation with no single owner is just data that drifts.

Constraints are part of the design

The schema is the last place that can say no, and the only one that cannot be bypassed:

CONSTRAINT ck_modules_position_positive CHECK (`position` <> 0),
CONSTRAINT fk_modules_course FOREIGN KEY (course_id)
  REFERENCES catalog_courses (id) ON DELETE CASCADE,
UNIQUE KEY uq_modules_position (course_id, `position`)

Application validation is a usability feature: it gives a good error message in the right language. Database constraints are a correctness feature: they hold when a migration script, an admin console or a direct UPDATE at two in the morning goes round the application entirely.

Write both. They are not alternatives.

Choosing types, briefly

Three decisions that are expensive to change later:

• Identifiers. A CHAR(36) UUID can be generated anywhere, which matters when several services write; a BIGINT auto-increment is smaller and faster to join, but centralises generation.
• Money. Integer minor units - price_cents INT - never a float. A float cannot represent 0.10 exactly, and the error compounds across a sum.
• Time. Store UTC, always, and convert at the edge. A DATETIME without a timezone that is sometimes local is a bug that only appears twice a year.