Keys & Normalization Basics

Primary keys and foreign keys: the mechanism

A primary key uniquely identifies each row in a table — no two rows can share one, and (on every mainstream engine) it can never be NULL. A foreign key is a column (or set of columns) in one table that references a primary key in another, and it's the mechanism that makes a JOIN meaningful in the first place: orders.customer_id being a foreign key to customers.id is why JOIN customers ON orders.customer_id = customers.id returns something meaningful rather than an arbitrary pairing.

Foreign keys are usually enforced with a constraint that prevents orphaned data outright — you can't insert an order with a customer_id that doesn't exist in customers — which is worth mentioning if asked "how would you prevent the exact bad-data scenario" that motivates a LEFT JOIN ... IS NULL anti-join query in the first place: a foreign key constraint prevents it at write time; the anti-join query detects it after the fact (or handles cases, like "no orders yet," that aren't actually bad data at all).

Why normalize: eliminating redundancy and the update anomalies it causes

Normalization is the process of splitting data into multiple related tables specifically to avoid storing the same fact in more than one place. The classic illustration: a single flat table with columns order_id, customer_name, customer_email, product_name, price repeats customer_name/customer_email on every single order that customer places. That repetition causes three concrete problems, each with a name:

• Update anomaly: a customer changes their email — you now have to update it in every order row, and missing even one leaves the data inconsistent.
• Insertion anomaly: you can't record a new customer until they place an order, because there's no table for customers to exist in independently.
• Deletion anomaly: deleting a customer's only order accidentally deletes all record that the customer ever existed.

Splitting into a customers table (one row per customer, referenced by customer_id) and an orders table (referencing customer_id as a foreign key) eliminates all three — the customer's email is stored exactly once, updatable in exactly one place.

The normal forms, at interview depth (not PhD depth)

You do not need to recite Boyce-Codd formal definitions in a coding interview — you need the practical gist of the first three:

• 1NF (First Normal Form): every column holds a single, atomic value — no comma-separated lists or nested structures crammed into one column (tags: "urgent,billing,vip" violates 1NF; a separate order_tags table with one tag per row satisfies it).
• 2NF: no column depends on only part of a composite primary key — relevant only when a table's primary key spans multiple columns; if a non-key column really only depends on one piece of that composite key, it belongs in a different table.
• 3NF: no column depends on another non-key column — the customer-email-repeated-on-every-order example above is precisely a 3NF violation, since customer_email depends on customer_id (itself not the orders table's key), not on order_id directly.

In practice, "is this schema roughly 3NF, and if not, why might that be a deliberate choice" is a far more realistic interview question than reciting formal definitions — which leads directly to the next point.

Denormalization is a deliberate trade-off, not a mistake

Normalization optimizes for write correctness and storage efficiency; it does not optimize for read speed. A fully normalized schema often requires several joins to answer a common read query, and at scale, that join cost is sometimes worse than the redundancy cost of storing a denormalized copy of some data (a total_order_count cached directly on the customers row, updated by a trigger or batch job, instead of computed with COUNT + JOIN on every read). Knowing that denormalization is a legitimate, common, deliberate choice — not just "doing it wrong" — is itself a senior-level signal; the honest answer to "should this be normalized" is almost always "it depends on the read/write ratio," not a reflexive "yes, always."

Why interviewers care

This subtopic is much more likely to come up as "why do you think this schema has these three tables instead of one" than as a formal normalization quiz — interviewers are checking whether you can read an unfamiliar schema and explain why it's shaped the way it is, since that's the same skill needed to correctly figure out which tables to join and how, on every other subtopic in this roadmap.

Pitfalls and interview gotchas

• Reciting formal normal-form definitions without being able to point to a concrete anomaly they prevent. The practical "what goes wrong without this" framing lands better than the textbook definition.
• Treating denormalization as an error rather than a legitimate, deliberate performance trade-off.
• Confusing a primary key with a unique index. A table can have several UNIQUE constraints but only one primary key; the primary key additionally implies NOT NULL and is (on most engines) what the table's default clustering/storage order is based on.

Where this goes next

Transactions, ACID & Isolation Levels (next) moves from "how is the data shaped" to "what happens when two queries touch that data at the same time" — the conceptual foundation for reasoning about correctness under concurrent access.