Database design: keys, normalisation, indexes and transactions

Keys and constraints

• Primary key: uniquely identifies a row; not null.
• Foreign key: references a primary key in another table; keeps relations valid (ON DELETE CASCADE or RESTRICT).
• Unique: no duplicates (UNIQUE (job_id, student_id): one application per student per job).
• NOT NULL, CHECK (score BETWEEN 1 AND 10), DEFAULT now().

Let the database enforce rules; application checks alone eventually let bad data in.

Relationships

• One-to-many: a company has many jobs (jobs.company_id).
• Many-to-many: students ↔ jobs through applications (a junction table).
• One-to-one: users ↔ student_profiles.

Normalisation

The goal: store each fact once, so updates can't create contradictions.

• 1NF: atomic values, no repeating groups (not skills = 'SQL, Excel, Python' if you need to query by skill; use a student_skills table).
• 2NF: 1NF, and every non-key column depends on the whole key (no partial dependency in composite keys).
• 3NF: 2NF, and non-key columns depend only on the key, not on other non-key columns (company city belongs in companies, not repeated in jobs).

Denormalisation (deliberately duplicating data) is fine for read-heavy reporting or caching counts, as long as you know how it stays in sync.

Indexes

An index (usually a B-tree) lets the database find rows without scanning the whole table, like a book's index.

CREATE INDEX applications_job_idx ON applications (job_id);
CREATE INDEX jobs_area_posted_idx ON jobs (area, posted_on DESC);  -- composite

• Index columns used in WHERE, JOIN and ORDER BY on large tables.
• A composite index (area, posted_on) helps queries filtering by area (leftmost prefix), and area + posted_on, but not posted_on alone.
• Indexes speed up reads but slow down writes and use space; don't index everything.
• Functions on a column defeat a normal index: WHERE LOWER(email) = ... needs an index on LOWER(email).
• EXPLAIN ANALYZE shows whether a query uses an index (Index Scan) or reads everything (Seq Scan).

Clustered vs non-clustered: a clustered index defines the physical order of the table (one per table, e.g. the primary key in MySQL InnoDB); non-clustered indexes are separate structures pointing to rows.

Transactions and ACID

• Atomicity: all statements succeed or none do.
• Consistency: constraints hold before and after.
• Isolation: concurrent transactions don't see each other's half-finished work.
• Durability: once committed, it survives a crash.

Isolation levels (weakest to strongest): Read Uncommitted, Read Committed (Postgres default), Repeatable Read, Serializable. Problems they prevent: dirty reads, non-repeatable reads, phantom reads.

DELETE vs TRUNCATE vs DROP

• DELETE FROM t WHERE …: removes chosen rows, logged row by row, can be rolled back, fires triggers.
• TRUNCATE t: removes all rows quickly, resets storage; minimal logging.
• DROP TABLE t: removes the table itself.

Check yourself

• Design tables for students, skills and the many-to-many link between them.
• Why might a query not use the index you created?
• Give an example where a missing transaction causes a real bug.