Stop 23 · Interview PreparationTechnical · 3/8

Database design & normalization

In short

"Design the schema for a hostel mess app" is a common interview task. Normalisation is how you avoid the classic bug: changing one fact in 300 places and missing a few.

Being able to design a sensible schema on the spot — identifying entities, relationships, and appropriate normalization for a given problem — a frequent interview exercise.

The purple blob sorts paper sheets into separate plain folders on a desk.

Think of it like this

One folder per kind of paper

If every meal slip also has the student's address and the warden's phone number written on it, changing the warden means rewriting hundreds of slips. Keep students in one folder, hostels in another, meals in a third, and link them.

Watch it happen

Cleaning up a messy mess register

Raw sheet

1NF

2NF

3NF

Indexes

1/5One big sheet: student, name, hostel, warden, date and a cell like "dal, rice, curd".

The big ideas

What you need to know

Why normalise

Repeated facts cause update, insert and delete anomalies. Store each fact once and reference it by key.

1NF, 2NF, 3NF in one line

Atomic values; no column depending on part of a key; no column depending on another non-key column.

Keys and constraints

Primary keys, foreign keys, UNIQUE and NOT NULL let the database reject bad data for you.

Denormalise on purpose

For read-heavy screens you may copy data, like a like_count. Do it knowingly and keep it in sync.

See it in code

Walk through the code

sql

1CREATE TABLE hostels (2  id     SERIAL PRIMARY KEY,3  name   TEXT NOT NULL UNIQUE,4  warden TEXT NOT NULL                  -- stored once, not on every student row5);6CREATE TABLE students (7  id        SERIAL PRIMARY KEY,8  name      TEXT NOT NULL,9  hostel_id INT NOT NULL REFERENCES hostels(id)10);11CREATE TABLE meals (12  student_id INT  NOT NULL REFERENCES students(id),13  meal_date  DATE NOT NULL,14  item       TEXT NOT NULL,15  PRIMARY KEY (student_id, meal_date, item)16);

1/3Each hostel and its warden live in exactly one row.

Changing a warden is now one UPDATE on one row. In the interview, also say which indexes you'd add, like meals (meal_date).

Save yourself the pain

Common mistakes

Oops

Storing lists as comma-separated text in one column.

Do this instead

That breaks 1NF and makes searching painful. Use a separate table, or a proper array or JSONB column if you really need it.

Oops

Normalising so far that every screen needs eight joins.

Do this instead

Aim for 3NF by default, then denormalise specific hot paths when you can measure the need.

Your mission

Normalise a real sheet

~35 min

Quick check

The warden's name is stored on all 300 student rows of a hostel. The warden changes and 3 rows are missed. What is this called?

Go deeper

1Practice designing a schema for a common prompt, e.g. a ride-sharing app, in 10 minutes.
2Be ready to justify a deliberate denormalization decision if asked.
3Know how to talk through indexes and their impact on a design.

More technical at this stop