Lesson 12 of 14 · 8 min · Database theory for interviews

Normalisation, keys and table design

Covers: Standard relational database theory with PostgreSQL syntax. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Primary, foreign, unique and composite keys
  • The three anomalies that bad table design causes
  • First, second and third normal form (and BCNF) with examples
  • How to normalise a messy table step by step
  • When denormalising is the right choice

In simple words

Imagine a school register where every row repeats the student's address. When a student moves house, you must fix every row, and if you miss one, the register disagrees with itself. Normalisation is the habit of storing each fact in exactly one place and pointing to it from elsewhere. That keeps the data consistent and the tables easy to change.

Words to know

  • Key: a column (or set of columns) that identifies rows.
  • Normal form: a rule a table design must satisfy, such as first or third normal form.
  • Anomaly: a problem caused by repeating data, such as an update that fixes one copy and misses another.
  • Dependency: when the value of one column is decided by another column.

Keys

  • Primary key (PK): uniquely identifies each row; never NULL; one per table (customer_id).
  • Candidate key: any column set that could serve as the primary key (for example customer_id, or email if unique).
  • Composite key: a primary key made of two or more columns, such as (order_id, product_id) in order_items.
  • Foreign key (FK): a column that must match a primary key in another table, enforced by the database (orders.customer_id REFERENCES customers). It stops orphan rows and can cascade deletes if you choose.
  • Unique key: no duplicates allowed, but (unlike a primary key) NULLs are allowed; a table can have several.
  • Surrogate key: an artificial id with no business meaning (auto-numbered); a natural key is a real-world value such as a PAN number.
CREATE TABLE enrollments (
  student_id INT NOT NULL REFERENCES students(student_id),
  course_id  INT NOT NULL REFERENCES courses(course_id),
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

The three anomalies

Here is one badly designed table:

order_id customer_name customer_city product price
1 Asha Hyderabad Notebook 100
2 Asha Hyderabad Pen 50
3 Ravi Pune Notebook 100
  • Update anomaly: Asha moves to Chennai. You must change every row; miss one and her city disagrees with itself.
  • Insert anomaly: you cannot record a new customer until they place an order, because there is no row without an order.
  • Delete anomaly: delete Ravi's only order and you lose the fact that Ravi exists and that a Notebook costs 100.

First normal form (1NF)

Each cell holds one atomic value, there are no repeating groups, and rows are identifiable.

Bad: a phones column holding 98480..., 99890..., or columns skill1, skill2, skill3. Good: one row per phone or per skill in a separate table:

student_skills(student_id, skill)

Second normal form (2NF)

The table is in 1NF and every non-key column depends on the whole key, not part of it. This only matters for composite keys.

order_items(order_id, product_id, quantity, product_name) has key (order_id, product_id), but product_name depends only on product_id. That is a partial dependency. Fix: move product_name into products(product_id, product_name).

Third normal form (3NF)

The table is in 2NF and no non-key column depends on another non-key column (no transitive dependency). Informally: every non-key column depends on "the key, the whole key and nothing but the key".

customers(customer_id, name, pincode, city) where city is determined by pincode: customer_id → pincode → city. Move pincode → city to its own table, so a city name is stored once.

BCNF

Boyce-Codd normal form is a stricter 3NF: for every dependency X → Y, X must be a candidate key. Cases where 3NF and BCNF differ are rare (overlapping composite candidate keys). In interviews, say 3NF removes transitive dependencies and BCNF closes the remaining loophole.

Normalising the example

Split the messy order table into three tables:

customers(customer_id PK, name, city)
products(product_id PK, name, price)
orders(order_id PK, customer_id FK, product_id FK)     -- or order_items for many products

Now Asha's city is stored once (no update anomaly), a customer can exist without orders (no insert anomaly), and deleting an order keeps the customer and product (no delete anomaly). The practice database in this course is designed like this.

Denormalisation on purpose

Normalised designs need joins, and joins cost time. Experienced designers sometimes denormalise, deliberately storing a copy:

  • Reporting and analytics: wide tables or star schemas (Power BI course) trade repeated values for simpler, faster queries.
  • Read-heavy paths: store order_total on the order so lists do not sum lines every time, and keep it correct in code or with a trigger.
  • Historical truth: an order should keep the price paid then, not the product's current price; copying it is correct design, not a shortcut.

The rule: normalise first; denormalise only for a measured need and know which copy is the source of truth.

Other design habits

  • Pick the right data types (DATE for dates, NUMERIC for money, not text or float).
  • Add NOT NULL, UNIQUE and CHECK constraints; let the database defend the data.
  • Name tables and columns consistently, and index foreign keys you join on.

Common mistakes

  • Storing lists in one column ('sql,excel,python').
  • Copying names instead of storing an id, which leads to update anomalies.
  • No foreign keys, letting orphan rows creep in.
  • Using money as FLOAT.
  • Over-normalising into dozens of tiny tables with no clear benefit.

Try it yourself

  1. Take a spreadsheet you have (marks, attendance, orders) and list the repeated facts.
  2. Normalise the messy order table above to 3NF and write the CREATE TABLE statements with keys.
  3. Add foreign keys with ON DELETE RESTRICT, then try to delete a customer who has orders.
  4. Name one place where you would keep a copy of a value on purpose, and say why.

Check yourself

What is the difference between a primary key and a unique key?

Both prevent duplicates, but a primary key cannot be NULL and a table has only one, while a unique key can allow NULL and a table can have several.

What problem does second normal form fix?

Partial dependency: a non-key column that depends on only part of a composite key.

What is a transitive dependency?

A non-key column that depends on another non-key column (key → B → C). Third normal form removes it.

Why would you denormalise?

To speed up reads or reporting by avoiding joins, or to keep a historical copy, accepting extra storage and the duty to keep copies consistent.

Interview questions

Explain 1NF, 2NF and 3NF with an example.

1NF: atomic values, no repeating groups. 2NF: 1NF plus no partial dependency on part of a composite key. 3NF: 2NF plus no transitive dependency between non-key columns. Example: moving product_name out of order_items fixes 2NF, and moving pincode → city out of customers fixes 3NF.

What are insert, update and delete anomalies?

Problems from redundant data: you cannot add a fact without unrelated data (insert), you must change many copies (update), or deleting a row loses unrelated facts (delete).

What is a foreign key and what does it enforce?

A column that refers to a primary or unique key in another table. It enforces referential integrity: no row can point to something that does not exist, and the database can restrict or cascade deletes.

Summary

  • Keys identify rows (primary, candidate, composite, unique) and link tables (foreign).
  • Bad design causes update, insert and delete anomalies.
  • 1NF atomic values, 2NF no partial dependency, 3NF no transitive dependency; BCNF is stricter.
  • Normalise first; denormalise only on purpose and with a clear source of truth.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on SQL for analysts and developers for Data Analyst and scores every answer, with what to fix.