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.