Schema design, indexes and transactions

2.1 From requirements to tables

A schema starts with a list of things the application must remember and the questions it must answer. The nouns usually become entities: a student, a course, an enrollment. The attributes become columns, and the verbs or links between entities become relationships with a cardinality: one to one, one to many, many to many. A many-to-many link needs its own table, which is the junction table from the joins module.

Before writing DDL, name the identity of each entity and the rule that keeps rows valid. Is an email unique? Can a course exist without an instructor? Can a student exist without any enrollment? Each answer becomes a column choice or a constraint. The schema is the place to enforce a rule once, instead of hoping every future query remembers it. Write the questions down next to the tables; future maintainers need the same list.

2.2 Normalization removes redundancy

Normalization is the process of arranging columns so each fact is stored once. First normal form requires atomic values and no repeating groups: one phone number per column, not a comma-separated list in one cell, and no columns named phone1, phone2, phone3. Second normal form requires that every non-key column depends on the whole composite key, not on part of it. In an enrollment row keyed by (student_id, course_code), storing the student's name would depend only on student_id and would violate 2NF.

Third normal form requires that non-key columns do not depend on other non-key columns. Storing course_title and fee next to course_code in the enrollment table is a transitive dependency: the title depends on the course, not on the enrollment. The same title and fee would repeat on every enrollment row, and changing the fee would mean updating many rows. A missed row leaves the table disagreeing with itself.

The three update anomalies are worth memorising as a test. An insert anomaly: a new course cannot be recorded until someone enrolls, because the course data lives only in enrollment rows. An update anomaly: a fee change has to touch every enrollment row. A delete anomaly: removing the last enrollment for a course also deletes the only copy of the course. Move the course columns into their own table and all three anomalies disappear.

Denormalization is a deliberate reversal for performance or reporting, and it needs a plan for keeping copies consistent. A summary table refreshed by a scheduled job is a common example. The mistake is denormalizing by accident and calling it performance tuning.

2.3 Constraints enforce the design

A constraint turns a design rule into something the engine checks on every write. NOT NULL refuses a missing value, UNIQUE refuses duplicate values, CHECK refuses a value outside a rule such as a negative fee, and PRIMARY KEY combines identity and uniqueness. A foreign key refuses a reference that does not exist, and the action decides what happens to children when the parent changes: RESTRICT or NO ACTION blocks it, CASCADE deletes or updates the children, and SET NULL clears the reference.

SQLite checks foreign keys only when PRAGMA foreign_keys = ON is set on the connection, which the practice database does at setup. The error block below exercises four constraints on purpose. Read each message as the engine repeating a design rule back to you: the enrollment with a missing student, the course with an unknown level, the duplicate email and the instructor deletion all fail, and none of them changes a row.

-- error demo: each statement below violates one constraint
INSERT INTO enrollments (student_id, course_code, enrolled_on)
VALUES (99, 'SQL', '2026-04-01');

INSERT INTO courses (course_code, title, level, fee)
VALUES ('X1', 'Test Course', 'expert', 100);

INSERT INTO students (student_id, name, email, joined_on)
VALUES (10, 'Copy Cat', 'aarav.sharma@example.com', '2026-04-01');

DELETE FROM instructors WHERE instructor_id = 1;

ERROR: FOREIGN KEY constraint failed
ERROR: CHECK constraint failed: level IN ('beginner', 'intermediate', 'advanced')
ERROR: UNIQUE constraint failed: students.email
ERROR: FOREIGN KEY constraint failed

2.4 Indexes make lookups cheap

A database index is a separate structure that keeps a sorted map from a key to the rows that hold it. The usual implementation is a B-tree, which gives logarithmic search for equality and range conditions and can also deliver rows in key order. Without an index the engine reads the table row by row, which is fine for ten rows and painful for ten million.

An index earns its keep when the column is used in WHERE, JOIN, ORDER BY or GROUP BY, and when it is selective enough that the index finds a small slice of the table. A composite index on (city, joined_on) can serve a filter on city, a filter on city and joined_on, and a sort by joined_on within a city, because the leftmost column comes first and the next column breaks ties. It cannot serve a query that filters only on joined_on. A covering index contains every column the query needs, so the engine answers from the index without visiting the table.

Indexes also cost something. Every insert, update and delete has to maintain each affected index, so a table with eight indexes pays eight writes for a row change. The plan below shows a scan and a temporary sort before any index exists on city. After the composite index is created, the same query becomes a search and the sort disappears because the index already returns rows in the required order.

EXPLAIN QUERY PLAN
SELECT name, joined_on FROM students
WHERE city = 'Delhi'
ORDER BY joined_on DESC;

CREATE INDEX idx_students_city_joined ON students(city, joined_on);

EXPLAIN QUERY PLAN
SELECT name, joined_on FROM students
WHERE city = 'Delhi'
ORDER BY joined_on DESC;

SCAN students
USE TEMP B-TREE FOR ORDER BY
SEARCH students USING INDEX idx_students_city_joined (city=?)

2.5 Transactions change data safely

A transaction groups several statements into one unit. BEGIN starts it, COMMIT makes every change permanent, and ROLLBACK undoes every change made since the start. SQLite runs each statement in an implicit transaction when no explicit one is open, and it allows one writer at a time, so a long write transaction blocks other writers. PostgreSQL runs many writers concurrently and uses row-level locking, and both engines offer isolation levels that trade consistency against concurrency.

The ACID properties describe the contract. Atomicity means all or nothing. Consistency means constraints hold at commit. Isolation means concurrent transactions do not see partial work. Durability means a committed change survives a crash. The transaction below changes a rating, reads the new value inside the transaction, rolls the change back, and reads the original value again. The same pattern protects a payment and an order update that must succeed together.

BEGIN;
UPDATE enrollments SET rating = 1
WHERE student_id = 1 AND course_code = 'SQL';
SELECT rating FROM enrollments WHERE student_id = 1 AND course_code = 'SQL';
ROLLBACK;
SELECT rating FROM enrollments WHERE student_id = 1 AND course_code = 'SQL';

rating
------
1
rating
------
5

2.6 A design checklist before writing DDL

Name every entity, its attributes and its identity. Draw each relationship and its cardinality, then decide which side owns the foreign key. List the rules that must always be true and map each one to a constraint. Normalize to 3NF unless a measured reason says otherwise. Add indexes for the queries you expect to be frequent and selective, not for every column. Wrap multi-step writes in a transaction, and test the failure path as well as the success path. Finally, run a handful of known queries and check the rows; a schema that cannot answer the application's questions is still wrong even when every constraint passes.

2.7 Common mistakes

• Storing several values in one column and calling the table normalized.
• Adding an index to every column and slowing down every write.
• Forgetting PRAGMA foreign_keys = ON in SQLite and assuming the foreign key was checked.
• Running multi-statement changes without a transaction and leaving partial data after an error.
• Denormalizing before measuring, then having no plan to keep the copies consistent.