CSE Roadmap

Specialist tier · about 3 hours · 6 parts

Part 2 of 6 · 2 min read

The schema

Think of the schema like a library's own catalog system. It sits beside a separate, much busier front desk logging which patron has checked out and returned which book.

In plain words: The platform's content, subjects, levels within a subject, and modules within a level, is read constantly but written rarely, since new modules are added by the team, not by learners. This content hierarchy fits a straightforward normalized relational design: a subjects table, a levels table with a foreign key to its subject, and a modules table with a foreign key to its level. Learner activity is the opposite: written constantly, every time a learner opens a module or finishes an exercise, and read whenever that learner, or an analytics job, needs to know their progress. A learners table and a progress table, recording one row per learner per module with a completion status and timestamp, keep this write-heavy activity cleanly separate from the rarely-changing content tables.

A worked example. A learner working through the Databases subject has one row in progress for each module they have started, updated as they move from "started" to "completed." The content tables themselves, subjects, levels, modules, never change because of anything a learner does.

The key lines.

CREATE TABLE subjects (
    subject_id   SERIAL PRIMARY KEY,
    slug         TEXT UNIQUE NOT NULL,
    title        TEXT NOT NULL
);

CREATE TABLE levels (
    level_id     SERIAL PRIMARY KEY,
    subject_id   INT REFERENCES subjects(subject_id),
    level_number INT NOT NULL,
    title        TEXT NOT NULL,
    UNIQUE (subject_id, level_number)
);

CREATE TABLE modules (
    module_id    SERIAL PRIMARY KEY,
    level_id     INT REFERENCES levels(level_id),
    module_number INT NOT NULL,
    title        TEXT NOT NULL,
    slug         TEXT NOT NULL,
    UNIQUE (level_id, module_number)
);

CREATE TABLE learners (
    learner_id   BIGSERIAL PRIMARY KEY,
    email        TEXT UNIQUE NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE progress (
    learner_id   BIGINT REFERENCES learners(learner_id),
    module_id    INT REFERENCES modules(module_id),
    status       TEXT NOT NULL DEFAULT 'started',  -- started, completed
    updated_at   TIMESTAMP NOT NULL DEFAULT now(),
    PRIMARY KEY (learner_id, module_id)
);

Content hierarchy, progress table

A content hierarchy is the rarely-written, frequently-read set of tables describing a platform's structure, here subjects, levels, and modules, linked by foreign keys. A progress table records one row per learner per unit of content, the write-heavy counterpart that tracks what each learner has actually done.

Example: the progress table's composite primary key, (learner_id, module_id), naturally enforces exactly one progress row per learner per module.