Chapter 12 · Normalization and Practical Schema Design
From Requirements to a Normalized Schema
A production schema is not discovered by drawing tables first. It emerges from facts, identifiers, cardinalities, lifecycle rules, and query needs, followed by normalization and constraint testing.
Learning outcomes
Translate narrative requirements into facts, entities, keys, and relationship cardinalities.
Derive functional dependencies from identifiers and lifecycle rules.
Normalize the design through 3NF and assess BCNF where relevant.
Implement the schema with SQLite keys, constraints, and referential actions.
Validate the model using sample transactions, requirement queries, and change scenarios.
Capstone requirements: a learning marketplace
Learners register with one unique email. Instructors create courses. A course may run many scheduled offerings. Each offering belongs to one course, has one lead instructor, and occurs in one term. Learners enroll once per offering. A payment may cover one enrollment, while free enrollments have no payment. Course tags support discovery.
| Requirement | Model implication |
|---|---|
| A learner email is unique. | learner.email is an alternate key. |
| A course has one creator but many offerings. | course references instructor; course_offering references course. |
| An offering has one lead instructor. | course_offering references instructor independently of course creator. |
| A learner enrolls once per offering. | Composite key or UNIQUE(offering_id, learner_id). |
| Tags are reusable across courses. | Many-to-many bridge course_tag. |
| Payment is optional and at most one per enrollment. | payment has a UNIQUE enrollment reference. |
Identify grains before columns
| Relation | One row represents | Candidate key |
|---|---|---|
| learner | One registered learner. | learner_id; email. |
| instructor | One instructor identity. | instructor_id; email. |
| course | One reusable course definition. | course_id; course_code. |
| course_offering | One scheduled run of a course. | offering_id; (course_id, term_code, section_no). |
| enrollment | One learner in one offering. | (offering_id, learner_id). |
| payment | One payment for one enrollment. | payment_id; enrollment reference. |
| tag | One reusable discovery tag. | tag_id; tag_name. |
| course_tag | One course-tag assignment. | (course_id, tag_id). |
Functional dependency inventory
learner_id -> learner_name, learner_emaillearner_email -> learner_id, learner_nameinstructor_id -> instructor_name, instructor_emailcourse_id -> course_code, title, creator_instructor_id(course_id, term_code, section_no) -> offering_id, lead_instructor_id, capacity(offering_id, learner_id) -> enrolled_at, status, final_gradepayment_id -> offering_id, learner_id, amount_cents, paid_at, provider_ref(offering_id, learner_id) -> payment_id [when a payment exists]tag_id -> tag_nametag_name -> tag_idOptional relationships need careful wording: an enrollment does not always determine a payment row, but the payment table can enforce at most one payment per enrollment with a unique composite foreign key.
Relationship map
Each arrow follows a foreign key; bridge tables represent independent many-to-many facts.
Implement the normalized schema
PRAGMA foreign_keys = ON;CREATE TABLE learner ( learner_id INTEGER PRIMARY KEY, learner_name TEXT NOT NULL CHECK (length(trim(learner_name)) > 0), learner_email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, instructor_name TEXT NOT NULL, instructor_email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, course_code TEXT NOT NULL UNIQUE, title TEXT NOT NULL, creator_instructor_id INTEGER NOT NULL REFERENCES instructor(instructor_id), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0,1))) STRICT;CREATE TABLE course_offering ( offering_id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL REFERENCES course(course_id), lead_instructor_id INTEGER NOT NULL REFERENCES instructor(instructor_id), term_code TEXT NOT NULL, section_no INTEGER NOT NULL CHECK (section_no > 0), capacity INTEGER NOT NULL CHECK (capacity > 0), UNIQUE (course_id, term_code, section_no)) STRICT;CREATE TABLE enrollment ( offering_id INTEGER NOT NULL REFERENCES course_offering(offering_id) ON DELETE CASCADE, learner_id INTEGER NOT NULL REFERENCES learner(learner_id) ON DELETE CASCADE, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','completed','withdrawn')), final_grade TEXT, PRIMARY KEY (offering_id, learner_id)) STRICT, WITHOUT ROWID;CREATE TABLE payment ( payment_id INTEGER PRIMARY KEY, offering_id INTEGER NOT NULL, learner_id INTEGER NOT NULL, amount_cents INTEGER NOT NULL CHECK (amount_cents >= 0), paid_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, provider_ref TEXT NOT NULL UNIQUE, UNIQUE (offering_id, learner_id), FOREIGN KEY (offering_id, learner_id) REFERENCES enrollment(offering_id, learner_id) ON DELETE RESTRICT) STRICT;CREATE TABLE tag ( tag_id INTEGER PRIMARY KEY, tag_name TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE course_tag ( course_id INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE, tag_id INTEGER NOT NULL REFERENCES tag(tag_id) ON DELETE CASCADE, PRIMARY KEY (course_id, tag_id)) STRICT, WITHOUT ROWID;Load a valid scenario
INSERT INTO instructor VALUES(31, 'Nadia Rahimi', 'nadia@example.edu'),(32, 'Omar Haddad', 'omar@example.edu');INSERT INTO learner VALUES(101, 'Ava Chen', 'ava@example.edu'),(102, 'Liam Ortiz', 'liam@example.edu');INSERT INTO course VALUES(501, 'SQL-101', 'SQL Foundations', 31, 1),(502, 'DB-201', 'Database Design', 32, 1);INSERT INTO course_offering VALUES(9001, 501, 31, '2026-S1', 1, 40),(9002, 502, 32, '2026-S1', 1, 30);INSERT INTO enrollment(offering_id, learner_id, status)VALUES(9001, 101, 'active'),(9001, 102, 'active'),(9002, 101, 'active');INSERT INTO payment(payment_id, offering_id, learner_id, amount_cents, provider_ref)VALUES(7001, 9001, 101, 4900, 'pay_A101'),(7002, 9002, 101, 7900, 'pay_A102');INSERT INTO tag VALUES(1, 'sql'), (2, 'database-design'), (3, 'beginner');INSERT INTO course_tag VALUES(501, 1), (501, 3), (502, 2);Verify requirement queries
SELECT c.course_code, c.title, i.instructor_name AS creator, GROUP_CONCAT(t.tag_name, ', ') AS tagsFROM course AS cJOIN instructor AS i ON i.instructor_id = c.creator_instructor_idLEFT JOIN course_tag AS ct ON ct.course_id = c.course_idLEFT JOIN tag AS t ON t.tag_id = ct.tag_idWHERE c.published = 1GROUP BY c.course_id, c.course_code, c.title, i.instructor_nameORDER BY c.course_code;SELECT o.offering_id, c.course_code, COUNT(e.learner_id) AS enrolled_count, o.capacity, ROUND(100.0 * COUNT(e.learner_id) / o.capacity, 1) AS occupancy_percent, COALESCE(SUM(p.amount_cents), 0) AS revenue_centsFROM course_offering AS oJOIN course AS c ON c.course_id = o.course_idLEFT JOIN enrollment AS e ON e.offering_id = o.offering_idLEFT JOIN payment AS p ON p.offering_id = e.offering_id AND p.learner_id = e.learner_idGROUP BY o.offering_id, c.course_code, o.capacityORDER BY o.offering_id;Test constraints with deliberate failures
-- Duplicate learner email.INSERT INTO learner VALUES(103, 'Duplicate Ava', 'ava@example.edu');-- Duplicate enrollment in the same offering.INSERT INTO enrollment (offering_id, learner_id)VALUES (9001, 101);-- Payment for a nonexistent enrollment.INSERT INTO payment(payment_id, offering_id, learner_id, amount_cents, provider_ref)VALUES (7003, 9002, 102, 7900, 'pay_invalid');-- Second payment for the same enrollment.INSERT INTO payment(payment_id, offering_id, learner_id, amount_cents, provider_ref)VALUES (7004, 9001, 101, 100, 'pay_duplicate');Constraint tests are part of schema verification. A design is incomplete until both valid and invalid states have been exercised.
Review normal forms
| Relation | Why it is normalized |
|---|---|
| learner | Both candidate keys determine the complete learner fact; no non-key determinant. |
| course | course_id and course_code identify the course; instructor data remains in instructor. |
| course_offering | The alternate composite key determines offering facts; course and instructor details are referenced. |
| enrollment | The composite key determines enrollment status and grade; no partial dependency. |
| payment | Payment facts depend on payment_id; uniqueness enforces at most one payment per enrollment. |
| course_tag | The composite key represents exactly one relationship fact and has no non-key attributes. |
Change scenario: team-taught offerings
A new requirement says an offering may have several instructors with roles. The old dependency offering_id → lead_instructor_id no longer captures the full relationship.
CREATE TABLE offering_instructor ( offering_id INTEGER NOT NULL REFERENCES course_offering(offering_id) ON DELETE CASCADE, instructor_id INTEGER NOT NULL REFERENCES instructor(instructor_id), role TEXT NOT NULL CHECK (role IN ('lead','assistant','guest')), PRIMARY KEY (offering_id, instructor_id), UNIQUE (offering_id, role)) STRICT, WITHOUT ROWID;The UNIQUE(offering_id, role) rule permits at most one instructor in each role. Remove or relax it if multiple assistants are allowed. Requirements determine dependencies; dependencies determine constraints.
Production schema review checklist
One sentence per table
Every table has a precise row meaning.
Natural and surrogate identity
Candidate keys and alternate keys are enforced.
Constraints at the boundary
Nullability, domains, uniqueness, and references match requirements.
Deletion and history
ON DELETE actions, retention, and status transitions are explicit.
Critical access paths
Representative reads and writes work without changing the fact model.
Future requirement tests
Likely cardinality changes can be modeled without hidden duplication.
Chapter 12 checkpoint
Defend the schema
- Why is offering separate from course?
- Why is enrollment keyed by offering and learner?
- How does payment enforce an optional one-to-one relationship?
- Which table resolves the course-tag many-to-many relationship?
- How would team teaching change the dependency model?
Review the answers
A course is reusable while an offering is a scheduled occurrence. The enrollment key prevents duplicate participation. A unique composite foreign key in payment permits zero or one payment per enrollment. course_tag stores each assignment. Team teaching replaces a single instructor attribute with an offering-instructor relationship whose key and role rules reflect the new cardinality.
Chapter 12 summary
- Requirements define facts, identifiers, dependencies, and lifecycle rules.
- Functional dependencies expose redundancy and guide decomposition.
- 1NF, 2NF, 3NF, and BCNF are design tests—not automatic goals detached from meaning.
- Denormalization is acceptable when the duplicate has an owner, freshness contract, reconciliation, and rebuild path.
- A schema is validated with both representative queries and intentional constraint failures.
Chapter 13 continues with transactions and concurrency: ACID boundaries, commit and rollback, isolation anomalies, locks, MVCC, deadlocks, retries, and idempotent operations.