Chapter 2 · Design and Integrity
Designing a Real Schema, and Changing It Safely
- Page 6 of 22
- 15 min read
The previous page gave you the rules; this one is the job. We design the database for a small learning platform — students, courses, lessons, enrolments and quiz attempts — from a page of requirements to working DDL, and test that the constraints really protect the data. Then we face what happens next in every real project: the schema has to change while the app is running and the data is already there.
This is not only a back-end concern. Quiz attempts on a learning platform are exactly the data that knowledge-tracing and recommendation models learn from, and a schema that keeps every attempt (instead of overwriting a "best score") is what makes that possible later.
What you will learn
- Turn requirements into entities, keys and constraints, and write the DDL
- Use composite foreign keys to enforce business rules ("only enrolled students take quizzes")
- Version schema changes as migration files and apply them with a tiny Python runner
- Make risky changes safely: add nullable, backfill, then constrain; expand and contract
- Know what Alembic, Flyway and Prisma Migrate do for you
The requirements
Students sign up with an email and a name. Courses have a unique code (like
SQL-101), a title and a level from 0 to 3. A course is an ordered list of lessons. Students enrol in courses; an enrolment is active, completed or dropped. Some lessons end with a quiz, which a student may attempt many times; we keep every attempt with its score (0–100) and time. Only enrolled students can attempt a course's quizzes.
Decisions, one by one
| Requirement | Design decision |
|---|---|
| "unique code", "email" | surrogate primary keys, plus UNIQUE on code and email |
| "ordered list of lessons" | lessons.position with UNIQUE (course_id, position): no two lesson 3s in one course |
| "students enrol in courses" | many-to-many → junction table enrolments, key (student_id, course_id) |
| "active, completed or dropped", "0–100", "level 0 to 3" | CHECK constraints, so bad values never get in |
| "keep every attempt" | quiz_attempts is append-only with its own id; best score is computed, not stored |
| "only enrolled students" | a composite foreign key from attempts to enrolments (student_id, course_id) |
STUDENTS ||--o{ ENROLMENTS : has
COURSES ||--o{ ENROLMENTS : has
COURSES ||--|{ LESSONS : "made of"
ENROLMENTS ||--o{ QUIZ_ATTEMPTS : "allows"
LESSONS ||--o{ QUIZ_ATTEMPTS : "quizzed in"The DDL
The tables go into shop.db next to the shop (no names clash). Types are written so the same DDL runs on SQLite and, with TEXT timestamps changed to TIMESTAMP, on PostgreSQL and MySQL:
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
joined_on DATE NOT NULL
);
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
code VARCHAR(20) NOT NULL UNIQUE,
title VARCHAR(200) NOT NULL,
level INTEGER NOT NULL CHECK (level BETWEEN 0 AND 3)
);
CREATE TABLE lessons (
lesson_id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL,
position INTEGER NOT NULL CHECK (position > 0),
title VARCHAR(200) NOT NULL,
has_quiz INTEGER NOT NULL DEFAULT 0 CHECK (has_quiz IN (0, 1)),
UNIQUE (course_id, position),
FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
CREATE TABLE enrolments (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
enrolled_on DATE NOT NULL,
status VARCHAR(10) NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'completed', 'dropped')),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (student_id),
FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
CREATE TABLE quiz_attempts (
attempt_id INTEGER PRIMARY KEY,
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
lesson_id INTEGER NOT NULL,
score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
attempted_at TEXT NOT NULL,
FOREIGN KEY (lesson_id) REFERENCES lessons (lesson_id),
FOREIGN KEY (student_id, course_id) REFERENCES enrolments (student_id, course_id)
);Foreign keys are written as table constraints (
FOREIGN KEY (…) REFERENCES …at the end of the table), not after the column. MySQL 8 accepts a column-levelREFERENCESand then silently ignores it; the table-level form is enforced by SQLite, PostgreSQL and MySQL alike.
Some sample data — two courses, three students, a few attempts:
INSERT INTO students VALUES
(1, 'nadia@example.com', 'Nadia Rahman', '2026-01-05'),
(2, 'rafiq@example.com', 'Rafiq Islam', '2026-01-20'),
(3, 'mitu@example.com', 'Mitu Das', '2026-02-02');
INSERT INTO courses VALUES (1, 'SQL-101', 'SQL for AI', 0), (2, 'ML-201', 'First ML Models', 1);
INSERT INTO lessons VALUES
(1, 1, 1, 'SELECT basics', 1), (2, 1, 2, 'Joins', 1), (3, 1, 3, 'Window functions', 1),
(4, 2, 1, 'Train/test split', 1), (5, 2, 2, 'Linear regression', 0);
INSERT INTO enrolments VALUES
(1, 1, '2026-01-06', 'completed'), (1, 2, '2026-03-01', 'active'),
(2, 1, '2026-01-21', 'dropped'), (3, 1, '2026-02-03', 'active');
INSERT INTO quiz_attempts VALUES
(1, 1, 1, 1, 60, '2026-01-07 10:00'), (2, 1, 1, 1, 90, '2026-01-08 09:30'),
(3, 1, 1, 2, 85, '2026-01-12 20:15'), (4, 1, 1, 3, 70, '2026-01-20 21:00'),
(5, 2, 1, 1, 40, '2026-01-22 18:00'), (6, 3, 1, 1, 95, '2026-02-04 08:45'),
(7, 3, 1, 2, 55, '2026-02-10 19:10'), (8, 1, 2, 4, 80, '2026-03-02 11:00');Do the constraints protect the data?
A schema is only as good as what it refuses. Try three bad writes:
-- Rafiq never enrolled in ML-201
INSERT INTO quiz_attempts VALUES (9, 2, 2, 4, 75, '2026-03-05 10:00');Error: FOREIGN KEY constraint failed-- A second lesson 2 in SQL-101
INSERT INTO lessons VALUES (6, 1, 2, 'Joins again', 0);Error: UNIQUE constraint failed: lessons.course_id, lessons.position-- A score out of range
INSERT INTO quiz_attempts VALUES (9, 3, 1, 3, 120, '2026-02-12 19:00');Error: CHECK constraint failed: score BETWEEN 0 AND 100Three bugs that would otherwise have reached the data, stopped at the door. Now the questions the platform actually asks — progress per enrolment, from the raw attempts:
SELECT s.name, c.code, e.status,
COUNT(DISTINCT qa.lesson_id) AS quizzes_tried,
(SELECT COUNT(*) FROM lessons AS l
WHERE l.course_id = c.course_id AND l.has_quiz = 1) AS quizzes_total,
COUNT(qa.attempt_id) AS attempts,
ROUND(AVG(qa.score), 1) AS avg_score
FROM enrolments AS e
JOIN students AS s ON s.student_id = e.student_id
JOIN courses AS c ON c.course_id = e.course_id
LEFT JOIN quiz_attempts AS qa
ON qa.student_id = e.student_id AND qa.course_id = e.course_id
GROUP BY s.student_id, s.name, c.course_id, c.code, e.status
ORDER BY s.student_id, c.code;+--------------+---------+-----------+---------------+---------------+----------+-----------+
| name | code | status | quizzes_tried | quizzes_total | attempts | avg_score |
+--------------+---------+-----------+---------------+---------------+----------+-----------+
| Nadia Rahman | ML-201 | active | 1 | 1 | 1 | 80.0 |
| Nadia Rahman | SQL-101 | completed | 3 | 3 | 4 | 76.3 |
| Rafiq Islam | SQL-101 | dropped | 1 | 3 | 1 | 40.0 |
| Mitu Das | SQL-101 | active | 2 | 3 | 2 | 75.0 |
+--------------+---------+-----------+---------------+---------------+----------+-----------+Because every attempt is kept, you can also ask what a "best score" column could never answer: did students improve on a second try? How long between attempts? Those are the features a model of student learning needs.
Changing a schema: migrations
The day after launch, someone asks for a new column. You cannot drop and recreate the database: it holds real data, and every developer's laptop, the test server and production must end up with the same schema. The answer is migrations: numbered SQL files, each one a single change, kept in version control and applied in order. The database records which ones it has already run.
migrations/
0001_initial.sql
0002_add_country.sql
0003_backfill_country.sql
0004_country_not_null.sqlA tiny migration runner
Real tools do more, but the core fits on one screen. It keeps a schema_migrations table, skips files already applied, and runs each new file inside a transaction so that a failing migration leaves nothing half-done. To keep the shop untouched it uses its own file, platform.db, and a cut-down version of the schema:
import sqlite3
from pathlib import Path
def migrate(db_path, folder="migrations"):
con = sqlite3.connect(db_path)
con.execute("CREATE TABLE IF NOT EXISTS schema_migrations ("
"version TEXT PRIMARY KEY, applied_at TEXT DEFAULT CURRENT_TIMESTAMP)")
con.commit()
done = {row[0] for row in con.execute("SELECT version FROM schema_migrations")}
applied = 0
for path in sorted(Path(folder).glob("*.sql")):
if path.stem in done:
continue
try:
con.executescript("BEGIN;\n" + path.read_text(encoding="utf-8"))
con.execute("INSERT INTO schema_migrations (version) VALUES (?)", (path.stem,))
con.commit()
print("applied", path.stem)
applied += 1
except sqlite3.Error as exc:
con.rollback()
print(f"FAILED {path.stem}: {exc} -- rolled back")
break
con.close()
print(f"{applied} migration(s) applied")
Path("migrations").mkdir(exist_ok=True)
Path("migrations/0001_initial.sql").write_text("""
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
INSERT INTO students (student_id, email, name) VALUES
(1, 'nadia@example.com', 'Nadia Rahman'),
(2, 'rafiq@example.com', 'Rafiq Islam');
""", encoding="utf-8")
migrate("platform.db")
migrate("platform.db")applied 0001_initial
1 migration(s) applied
0 migration(s) appliedThe second run does nothing: running the migrator is idempotent (each file is applied once, ever), so every environment can simply run "migrate" on every deploy.
The safe way to add a required column
Say the shop now wants a required total_amount on every order, computed from its items. The tempting single step — add a NOT NULL column — goes wrong on a table that already has rows, and differently in each database:
-- PostgreSQL: refuses, because existing rows would be NULL
ALTER TABLE orders ADD COLUMN total_amount INTEGER NOT NULL;Error: column "total_amount" of relation "orders" contains null values-- MySQL: succeeds, silently filling every row with 0
ALTER TABLE orders ADD COLUMN total_amount INTEGER NOT NULL;
SELECT order_id, total_amount FROM orders WHERE order_id <= 3 ORDER BY order_id;+----------+--------------+
| order_id | total_amount |
+----------+--------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
+----------+--------------+MySQL's version is worse: the totals look like real data. The safe pattern is three steps — add nullable, backfill, then constrain:
-- PostgreSQL: 1. add nullable 2. backfill from the real data 3. constrain
ALTER TABLE orders ADD COLUMN total_amount INTEGER;
UPDATE orders AS o
SET total_amount = (SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
FROM order_items AS oi WHERE oi.order_id = o.order_id)
WHERE total_amount IS NULL;
ALTER TABLE orders ALTER COLUMN total_amount SET NOT NULL;
SELECT order_id, total_amount FROM orders WHERE order_id <= 3 ORDER BY order_id;+----------+--------------+
| order_id | total_amount |
+----------+--------------+
| 1 | 2100 |
| 2 | 4500 |
| 3 | 3000 |
+----------+--------------+On a big table you would run step 2 in batches (WHERE order_id BETWEEN …), each committed on its own, so no single transaction holds locks on millions of rows and blocks other writers for long. Step 3 still scans the whole table to check for NULLs. When the new value is a constant, PostgreSQL 11+ and MySQL 8 can add NOT NULL DEFAULT 'BD' instantly; the three steps matter when each row's value has to be computed.
Back in our runner, students needs a required country. The same three steps become three migration files. SQLite cannot add NOT NULL to an existing column, so step 3 uses the standard SQLite recipe: build a new table, copy, drop, rename — all inside the migration's transaction:
Path("migrations/0002_add_country.sql").write_text(
"ALTER TABLE students ADD COLUMN country TEXT;", encoding="utf-8")
Path("migrations/0003_backfill_country.sql").write_text(
"UPDATE students SET country = 'BD' WHERE country IS NULL;", encoding="utf-8")
Path("migrations/0004_country_not_null.sql").write_text("""
CREATE TABLE students_new (
student_id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
country TEXT NOT NULL DEFAULT 'BD'
);
INSERT INTO students_new (student_id, email, name, country)
SELECT student_id, email, name, country FROM students;
DROP TABLE students;
ALTER TABLE students_new RENAME TO students;
""", encoding="utf-8")
Path("migrations/0005_unique_name.sql").write_text("""
INSERT INTO students (student_id, email, name) VALUES (3, 'nadia2@example.com', 'Nadia Rahman');
CREATE UNIQUE INDEX students_name ON students (name);
""", encoding="utf-8")
migrate("platform.db")
con = sqlite3.connect("platform.db")
print(con.execute("SELECT group_concat(version, ', ') FROM schema_migrations").fetchone()[0])
print(con.execute("SELECT * FROM students ORDER BY student_id").fetchall())
con.close()applied 0002_add_country
applied 0003_backfill_country
applied 0004_country_not_null
FAILED 0005_unique_name: UNIQUE constraint failed: students.name -- rolled back
3 migration(s) applied
0001_initial, 0002_add_country, 0003_backfill_country, 0004_country_not_null
[(1, 'nadia@example.com', 'Nadia Rahman', 'BD'), (2, 'rafiq@example.com', 'Rafiq Islam', 'BD')]Migration 0005 failed halfway — its insert had already run when the unique index hit the duplicate name — and the transaction undid all of it: no third student, no row in schema_migrations, so it will run again once fixed. That all-or-nothing behaviour depends on the database: SQLite and PostgreSQL can roll back DDL. MySQL cannot: it ends the open transaction with an implicit commit before and after every DDL statement (CREATE, ALTER, DROP …), so everything up to the failure stays applied, even an INSERT that ran before the DDL. MySQL 8 makes each single DDL statement atomic, but not a group of them. Keep MySQL migrations to one DDL statement each.
Expand and contract: changes with zero downtime
Renaming or retyping a column breaks every running copy of the app that still uses the old name. With several app servers deploying one by one, there is always a moment when old and new code run together. The expand/contract (parallel change) pattern keeps both working:
| Step | Database | Application |
|---|---|---|
| 1. Expand | add full_name (nullable) | old code keeps using name |
| 2. Dual write | — | deploy code that writes both columns |
| 3. Backfill | copy name into full_name for old rows | — |
| 4. Switch reads | add NOT NULL to full_name | deploy code that reads only full_name |
| 5. Contract | drop name | (nothing uses it any more) |
Each step is its own migration and its own deploy, and every step can be rolled back on its own. It feels slow; it is how teams change big production schemas without an outage.
Migration tools
| Tool | World | How it works |
|---|---|---|
| Alembic | Python / SQLAlchemy | Python migration scripts with upgrade()/downgrade(); can autogenerate a draft by diffing your models against the database |
| Django migrations | Django | generated from model changes with makemigrations, applied with migrate |
| Flyway | any (CLI, JVM) | plain SQL files named V1__init.sql, V2__add_country.sql (two underscores); a history table like our runner's, plus a checksum of each file |
| Prisma Migrate | TypeScript / Node | edit schema.prisma; migrate dev generates SQL migration files from the diff and applies them to your development database; migrate deploy only applies them, in production |
alembic revision --autogenerate -m "add country to students" # draft a migration
alembic upgrade head # apply all pending
flyway migrate # apply pending V*.sql files
npx prisma migrate dev --name add_country # generate and apply (development)
npx prisma migrate deploy # apply pending ones (production)Autogenerated migrations are drafts: always read them. A diff cannot reliably tell a rename from a drop-plus-add: Alembic and Prisma write a drop and an add (Prisma at least warns about data loss), Django asks you. That difference is your data.
Common mistakes
- Editing a migration that already ran somewhere. The database records the version as done and never re-runs it, so environments drift apart (Flyway notices the changed checksum and refuses to continue). Write a new migration instead.
- Adding
NOT NULLin one step on a full table. PostgreSQL refuses; MySQL fills zeros or empty strings. Add nullable, backfill, then constrain. - Storing derived values that can drift. A
best_scorecolumn next to the attempts must be kept in sync forever. Compute it in a query or a view unless measurements say otherwise. - Several DDL statements in one MySQL migration. MySQL commits DDL implicitly, so a failure leaves it half-applied. One DDL statement per migration file.
- Renaming a column in place under live traffic. Old app servers break immediately. Expand, dual-write, backfill, switch, contract.
Try it yourself
- Easy: Show each student's best score per quiz lesson in SQL-101 (student name, lesson title, best score), ordered by student and lesson position.
- Medium: Write migration
0005_unique_name.sqlagain, correctly this time: drop the bad insert and add a plain (non-unique) index onname, then run the runner and print the applied versions. - Hard:
quiz_attemptsdoes not stop an attempt whoselesson_idbelongs to a different course than itscourse_id. How would you enforce it with a constraint? Write the DDL changes (you may use new table names).
Answers
-- Easy
SELECT s.name, l.title, MAX(qa.score) AS best_score
FROM quiz_attempts AS qa
JOIN students AS s ON s.student_id = qa.student_id
JOIN lessons AS l ON l.lesson_id = qa.lesson_id
WHERE qa.course_id = 1
GROUP BY s.student_id, s.name, l.lesson_id, l.position, l.title
ORDER BY s.student_id, l.position;# Medium
Path("migrations/0005_unique_name.sql").write_text(
"CREATE INDEX students_name ON students (name);", encoding="utf-8")
migrate("platform.db")
con = sqlite3.connect("platform.db")
print(con.execute("SELECT group_concat(version, ', ') FROM schema_migrations").fetchone()[0])
con.close()-- Hard: make (lesson_id, course_id) referenceable, then reference both columns
CREATE TABLE lessons_v2 (
lesson_id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL,
position INTEGER NOT NULL CHECK (position > 0),
title VARCHAR(200) NOT NULL,
UNIQUE (course_id, position),
UNIQUE (lesson_id, course_id),
FOREIGN KEY (course_id) REFERENCES courses (course_id)
);
CREATE TABLE quiz_attempts_v2 (
attempt_id INTEGER PRIMARY KEY,
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
lesson_id INTEGER NOT NULL,
score INTEGER NOT NULL CHECK (score BETWEEN 0 AND 100),
attempted_at TEXT NOT NULL,
FOREIGN KEY (student_id, course_id) REFERENCES enrolments (student_id, course_id),
FOREIGN KEY (lesson_id, course_id) REFERENCES lessons_v2 (lesson_id, course_id)
);Summary
- Go requirement by requirement: each one becomes a key, a junction table, a
CHECKor a foreign key — and then test that bad writes are refused. - Composite foreign keys encode rules that span tables; keep raw events (every attempt) and compute aggregates from them.
- Migrations are numbered, version-controlled change files with a history table; never edit one that has run.
- Required columns on existing tables: add nullable, backfill, constrain. Live renames: expand and contract.
- Alembic, Flyway, Prisma and Django automate the bookkeeping; you still read every migration.
Next: Transactions, ACID, Isolation and Locking looks closely at the transactions this page relied on: what BEGIN/COMMIT/ROLLBACK guarantee, what goes wrong when two sessions change the same rows, and how locks and retries fix it.