Schema Design & Normalization

A table is in first normal form when every column holds one single value of its declared type — no lists, no nested structures, no repeating groups such as phone1/phone2/phone3 — and every row is uniquely identifiable, normally by a primary key.

No. product_name depends only on product_id and order_date only on order_id, both parts of the composite key, so both are partial dependencies. Split into ORDER(order_id, order_date), PRODUCT(product_id, product_name), and ORDER_LINE(order_id, product_id, quantity).

No. dept_id determines dept_name and dept_location, so both depend on the key only through a non-key attribute. Split out DEPARTMENT(dept_id, dept_name, dept_location) and leave EMPLOYEE(emp_id, emp_name, dept_id) with a foreign key.

Repeating a fact means you cannot record it without an unrelated row (insertion anomaly), must rewrite every copy to change it (update anomaly, with drift if partial), and lose it when the last carrier row is deleted (deletion anomaly). Move the fact to its own table with a foreign key.

BCNF requires that for every non-trivial functional dependency X to Y in a table, X is a superkey. The determinant is X, the left-hand side. Nothing but a superkey may determine anything.

An entity is a thing with its own identity, its own describing facts and its own lifecycle. An attribute is one fact about a single entity instance. If addresses repeat, need describing, or are referenced on their own, model an entity.

It becomes a third table holding one row per pair — student key and course key — with a foreign key to each parent and a primary key over the pair. Neither parent can hold the link because each side has many partners, and a column stores only one value.

It becomes a child table holding the owner's key plus one value per row, with a foreign key to the owner and a key over (owner key, value). Repeated columns cap the count and scatter one fact; a delimited string loses typing, constraints and lookups.

Give the table a parent_id column that is a foreign key back to its own primary key. Roots have NULL parent_id. One row per node stores one edge. To read a whole subtree you walk the parent_id chain repeatedly, which in standard SQL means a recursive query.

Use a third table holding one foreign key to each side, keyed on the pair of them. A single foreign key column stores one value per row, so it cannot record many links from the same row.

Numbered columns hard-code a limit, force every query to repeat itself across all three columns, cannot be indexed or constrained as a group, and need a schema change to add a fourth. Replace them with a child table holding one row per phone, keyed to the parent.

A natural key is business data that already identifies the row (email, ISBN, country code). A surrogate key is a meaningless generated value (a sequence integer or UUID). Default to a surrogate for stability and narrowness, and keep the natural key as a UNIQUE constraint.

Soft delete marks a row as gone — usually by setting a deleted_at timestamp — instead of removing it. The row still physically exists, so it can be restored and still satisfies foreign keys, but every query must now filter it out and the table keeps growing.

Add a history table that mirrors the live table's columns plus its own id, the changed row's id, changed_at, changed_by and the operation (insert/update/delete). Write one append-only row per change, inside the same transaction as the change.

Application checks are racy and only cover the paths that implement them - backfills, admin tools and other services bypass them - so orphan rows accumulate silently. Foreign keys make the invariant unconditional. Skipping them is defensible mainly across separate datastores or shards, where the constraint is not expressible anyway.

A history table inside the same database (Flyway's flyway_schema_history, Liquibase's DATABASECHANGELOG) holds one row per applied script: version or id, description, checksum, timestamp, success flag. Each run diffs the scripts on disk against those rows and applies only the missing ones.

Expand: add the new columns and make the app write both. Migrate: backfill existing rows in batches, then switch reads to the new columns. Contract: stop writing the old column and drop it. Every step must work with the previous version still running.

The stored checksum no longer matches the edited file, so validation fails on every database that already ran it, while a fresh database gets the corrected version — the two diverge. Applied migrations are immutable: revert the edit and ship a new migration containing the fix.

Anything that rewrites the table or holds an exclusive lock: type changes, most index builds, validating new constraints, some column additions. Use concurrent or online index builds, add-then-validate constraints, a short lock timeout with retries, and batched work.

Rollback is viable only for additive, reversible changes that no code or data depends on yet — usually within minutes. Once a drop or a data transformation ran, or new code wrote data in the new shape, undoing loses information, so you forward-fix with a new migration and, if needed, roll back the application.