This page needs a newer browser to run its tools. You can still read everything here.

SQL (PostgreSQL, MySQL, SQLite) Module 13

Designing database schemas interview questions

Interview questions on designing database schemas: short warm-ups, then what each harder question tests, the first step of a good answer and its follow-ups.

19 questions: 5 warm-ups and 14 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

What is a weak entity, and how does it become a table?

Answered in the lesson From requirements to an ER model
Fresher to mid

What is a functional dependency, and how do you check one in a table's data?

Answered in the lesson Normalisation: 1NF to BCNF in practice
Fresher to mid

What is denormalisation, and what does it cost?

Answered in the lesson Denormalisation and derived data
Fresher to mid

What is a junction table, and what goes into its primary key?

Answered in the lesson Many-to-many, one-to-one and polymorphic links
Fresher to mid

Name the four common ways to store a tree in SQL tables, and say what each one stores.

Answered in the lesson Storing trees: adjacency, paths and closure tables

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Fresher to mid

You have 15 minutes to turn this brief into tables: an inter-college hackathon has teams of 2 to 4 students, each team picks one problem statement and submits up to 3 versions of its project, and three judges score every submission. Walk through your method and the tables you end with.
What it tests

Getting from a brief to entities, cardinality read from both ends, weak entities and junction tables with attributes, and naming the rules that keys cannot enforce.

First step

List what the organisers will ask: which teams are complete, which judge has not scored which submission, the ranking per problem statement. Then mark the nouns: team, student, problem, submission, judge, score. A score belongs to a pair of a judge and a submission, so it is a junction table whose key is that pair.
Follow-ups
- How do you enforce teams of 2 to 4 students?
- A student moves to another team halfway through. What changes?
- Where does a submission's version number come from?
From the lesson From requirements to an ER model
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Your ER diagram says that every order line belongs to exactly one order and that every order has at least one line. Which of the two rules do your foreign keys enforce, and how do you enforce the other in PostgreSQL, MySQL and SQLite?
What it tests

The difference between the maximum and the minimum of a relationship, what a foreign key checks, deferred checks at commit, and engine differences.

First step

The NOT NULL foreign key on order_lines.order_id enforces the first rule: each line names one existing order. No row-level constraint can enforce the second, because a new order has no lines at the moment it is inserted. The rule can only hold at the end of a transaction, so look for a check that runs at commit.
Follow-ups
- Would a CHECK constraint with a subquery do it?
- What happens when lines are deleted one by one?
- How do you find the orders that already break the rule?
From the lesson From requirements to an ER model
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

You are about to split a 2-million-row export on the dependency company -> ctc, and a check finds 1,214 companies whose rows hold two or more CTC values. What do you do before splitting, and how do you decide which value is right?
What it tests

Reading dependency violations, telling formatting from real disagreement, questioning the dependency itself, and repairing data with evidence and a record.

First step

Look before fixing: for a sample of the 1,214 companies, list each CTC value with its row count and its dates. Separate formatting and unit differences, such as 8 against 8.0 or 80 against 8.0, from real disagreements, and ask whether the CTC depends on the company alone or on the company and the role.
Follow-ups
- Most of the violations come from companies that run several drives a year. What does that tell you?
- How do you make sure the repaired table cannot drift again?
- Would you split the table first and clean up afterwards?
From the lesson Normalisation: 1NF to BCNF in practice
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Normalise exam_results(roll_no, student_name, branch, branch_hod, subject_code, subject_name, credits, marks) to BCNF, naming every dependency and key. Each student takes each subject once, and marks belong to a student and a subject.
What it tests

Finding dependencies and the candidate key, telling partial from transitive dependencies, and producing BCNF tables with their keys and foreign keys.

First step

Write the dependencies first: roll_no -> student_name, branch; branch -> branch_hod; subject_code -> subject_name, credits; and (roll_no, subject_code) -> marks. Nothing smaller determines marks, so (roll_no, subject_code) is the key, and every other dependency is a violation to move out.
Follow-ups
- Which normal form does the original table break first, and through which columns?
- Credits now depend on the subject and the syllabus year. What changes?
- How do you show that your tables join back to the original rows?
From the lesson Normalisation: 1NF to BCNF in practice
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Senior to staff

A 40-column orders table repeats the customer's name, email and city on every order, and dozens of reports read it directly. Plan the move to a normalised design without breaking the reports or losing data. What do you check at each step?
What it tests

Planning a schema change in reversible steps, compatibility views and triggers, verifying a backfill, and separating real dependencies from facts that belong to each order.

First step

Measure the drift first: per customer, count the distinct names, emails and cities across their orders. Then plan steps that each keep the reports working: create and fill customers, verify the backfill with EXCEPT in both directions and equal counts, and only then put a view with the old columns under the old name.
Follow-ups
- A customer has three different emails on their orders. Which one goes into customers?
- The database is MySQL 8.4. What changes in the plan?
- When can the old columns be dropped?
From the lesson Normalisation: 1NF to BCNF in practice
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

Support reports that some products show more reviews than their pages list. products.review_count is kept by triggers on reviews. How do you find every wrong product, repair them, and find the change that caused the drift?
What it tests

Recompute audits, repairs that leave a record, the paths that bypass row triggers in each engine, and preventing a repeat.

First step

Run one audit over all products: group reviews by product, LEFT JOIN the counts to products, and keep the rows where review_count differs from the fresh count, saved to a table. The pattern of the differences, too high or too low, and their dates, points to the path that skipped a trigger.
Follow-ups
- The counts are too high only for products whose review authors deleted their accounts. Why?
- How do you repair 2 million products without blocking writes for long?
- What stops the drift from coming back?
From the lesson Denormalisation and derived data
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

A dashboard of daily revenue per city reads a 200-million-row orders table and takes 40 seconds. Compare a summary table kept by triggers, one rebuilt by a nightly job and a PostgreSQL materialised view, with numbers for freshness and write cost, and choose one.
What it tests

Sizing the derived data, the write and read cost of each mechanism, freshness requirements, late corrections and refresh locking.

First step

Size the summary first: one row per city and day, so 50 cities over three years are about 55,000 rows instead of 200 million. Then ask how fresh the dashboard must be: if yesterday is enough, a nightly rebuild costs no write at all; if today's figures matter, each order must touch the summary.
Follow-ups
- Finance corrects orders up to 30 days after the fact. What changes?
- The dashboard must show today's revenue too. Which design now?
- Why might REFRESH MATERIALIZED VIEW CONCURRENTLY fail, and what does it cost when it works?
From the lesson Denormalisation and derived data
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

Every checkout updates one row, stats.orders_today, inside the order's transaction, and at sale-time peaks checkout latency climbs from 80 ms to 2 seconds while CPU stays low. Explain the cause, and redesign the counter.
What it tests

Row locks on a hot row, how long a transaction holds them, lost updates, slot counters, event tables and what each design costs readers.

First step

Low CPU with rising latency means waiting, not working. Every checkout updates the same row, so each waits for the previous transaction to commit before its own update can proceed. Measure how long a checkout holds the row, from its update to its commit: one row admits at most one second divided by that time.
Follow-ups
- Why not read the counter, add one in the application and write it back?
- How many slots do you need, and what happens to reads?
- The counter is shown on the home page and may be a few seconds old. What is the simplest design?
From the lesson Denormalisation and derived data
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A comments table uses commentable_type ('product' or 'order') and commentable_id. A product was deleted and its comments remained, and some comments point at orders that never existed. Explain why no constraint stopped this, and redesign the table so that the database does.
What it tests

Why a foreign key cannot follow a type column, the three designs that restore checks, their trade-offs, and moving existing data without losing comments.

First step

A foreign key names one parent table, and commentable_id points into a table chosen by another column of the row, so no foreign key exists to refuse a delete or a wrong id. Count the damage with one outer join per type, then pick a design in which every comment has a real foreign key.
Follow-ups
- The application will add comments on sellers and blog posts next year. Which design do you choose?
- The database is MySQL 8.4 and deleting a product must delete its comments. What changes?
- How do you move the existing comments without a long outage?
From the lesson Many-to-many, one-to-one and polymorphic links
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Fresher to mid

Students may take a course again after failing it. Your enrolments table has PRIMARY KEY (student_id, course_id), and the second enrolment fails with a unique-constraint error. A teammate proposes updating the old row instead. What do you do?
What it tests

Choosing a junction table's key from what makes a row unique, keeping history, and enforcing the remaining rule with a partial unique index.

First step

Refuse the update: it overwrites the failed attempt, which the transcript must keep. Ask what makes an enrolment unique, the student, the course and the term, and make that the key. Then write down the rule that remains, such as one open attempt at a time, and enforce it separately.
Follow-ups
- How do you enforce "at most one attempt in progress per student and course"?
- Would a surrogate enrolment_id remove the problem?
- How do the transcript queries change?
From the lesson Many-to-many, one-to-one and polymorphic links
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes
Mid to senior

Product tags are stored as a comma-separated text column, and a teammate suggests a PostgreSQL text array instead. When is a list in one column acceptable, and when must it become a junction table? Use the queries the shop runs to decide.
What it tests

What a list in a column cannot do, foreign keys on elements, indexing and searching lists, and deriving a list from a junction table when an API needs it.

First step

List the questions asked of the tags: products with tag X, counts per tag, renaming or deleting a tag. Each of them treats a tag as a thing of its own, which a list in a cell does not; and no foreign key can check the elements of a text list or an array.
Follow-ups
- Is a GIN index on the array enough to make it acceptable?
- The API returns each product with its tags. Where does that list come from?
- When is a list in one column the right design?
From the lesson Many-to-many, one-to-one and polymorphic links
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

An online store has 8,000 categories up to six levels deep. Every product page shows a breadcrumb, every category page lists the products of its whole subtree, and merchandisers move a few branches each week. Which tree model do you choose, and how do you keep it correct?
What it tests

Choosing a tree model from the read and write mix, estimating its size, and keeping derived tree data consistent with the parent links.

First step

Write the workload down first: two tree reads on almost every page view, a few moves a week. Reads win, so choose a model whose subtree read is one index range or one join, a materialised path or a closure table, and keep parent_id as the source of truth. Then estimate the closure table: at most seven rows per category, so under 56,000 rows.
Follow-ups
- A branch of 900 categories moves. What does each model write?
- How do you make sure the derived model never drifts from parent_id?
- A product may now sit in two categories. What changes?
From the lesson Storing trees: adjacency, paths and closure tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

A discussion forum stores each thread's comments as nested sets. On threads with 20,000 comments, posting a reply takes over a second, and at peak replies fail with lock timeouts. Explain why, and how you would move the forum to another model without downtime.
What it tests

Why nested-set inserts renumber and lock large parts of a tree, choosing a replacement model, and migrating derived data while writes continue.

First step

Work out what one reply writes. Its numbers go inside its parent's pair, so every comment to its right moves up by two, and so do its ancestors' right numbers: about half the thread, 10,000 row updates, locked until commit. Replies to the same thread queue behind each other. Then choose a model in which a reply writes a fixed number of rows.
Follow-ups
- Which model do you move to, and what does a reply cost there?
- How do you backfill and switch without stopping writes?
- How do you show a thread in reading order without nested-set numbers?
From the lesson Storing trees: adjacency, paths and closure tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to staff

A document app checks on every request whether a file lies inside any folder shared with the user. Folders nest up to 20 levels, a user can see thousands of shared folders, and folders are moved now and then. Design the tables and the check query, and say what a folder move costs.
What it tests

Turning an ancestor check into an indexed lookup with a closure table, joining it to permissions, and pricing moves in a deep tree.

First step

The question is whether any folder at or above the file's folder is shared with this user. With a closure table that is one join: the folder's ancestors, at most 21 rows read by descendant, against the user's shares, keyed by user and folder. Write that EXISTS query first, then price the writes.
Follow-ups
- What does moving a folder with 5,000 subfolders cost?
- How do you list everything a user can see, not just check one file?
- Would a materialised path work instead?
From the lesson Storing trees: adjacency, paths and closure tables
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes

The lessons of this module

From requirements to an ER model Free lesson, Pro answers: 30 minutes
Normalisation: 1NF to BCNF in practice Free core, Pro depth: 12 of 35 minutes free
Denormalisation and derived data Free core, Pro depth: 10 of 30 minutes free
Many-to-many, one-to-one and polymorphic links Free core, Pro depth: 10 of 30 minutes free
Storing trees: adjacency, paths and closure tables Free core, Pro depth: 10 of 30 minutes free

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track

Warm-up questions

What is a weak entity, and how does it become a table?

What is a functional dependency, and how do you check one in a table's data?

What is denormalisation, and what does it cost?

What is a junction table, and what goes into its primary key?

Name the four common ways to store a tree in SQL tables, and say what each one stores.

Questions with model answers

You have 15 minutes to turn this brief into tables: an inter-college hackathon has teams of 2 to 4 students, each team picks one problem statement and submits up to 3 versions of its project, and three judges score every submission. Walk through your method and the tables you end with.

Your ER diagram says that every order line belongs to exactly one order and that every order has at least one line. Which of the two rules do your foreign keys enforce, and how do you enforce the other in PostgreSQL, MySQL and SQLite?

You are about to split a 2-million-row export on the dependency company -> ctc, and a check finds 1,214 companies whose rows hold two or more CTC values. What do you do before splitting, and how do you decide which value is right?

Normalise exam_results(roll_no, student_name, branch, branch_hod, subject_code, subject_name, credits, marks) to BCNF, naming every dependency and key. Each student takes each subject once, and marks belong to a student and a subject.

A 40-column orders table repeats the customer's name, email and city on every order, and dozens of reports read it directly. Plan the move to a normalised design without breaking the reports or losing data. What do you check at each step?

Support reports that some products show more reviews than their pages list. products.review_count is kept by triggers on reviews. How do you find every wrong product, repair them, and find the change that caused the drift?

A dashboard of daily revenue per city reads a 200-million-row orders table and takes 40 seconds. Compare a summary table kept by triggers, one rebuilt by a nightly job and a PostgreSQL materialised view, with numbers for freshness and write cost, and choose one.

Every checkout updates one row, stats.orders_today, inside the order's transaction, and at sale-time peaks checkout latency climbs from 80 ms to 2 seconds while CPU stays low. Explain the cause, and redesign the counter.

A comments table uses commentable_type ('product' or 'order') and commentable_id. A product was deleted and its comments remained, and some comments point at orders that never existed. Explain why no constraint stopped this, and redesign the table so that the database does.

Students may take a course again after failing it. Your enrolments table has PRIMARY KEY (student_id, course_id), and the second enrolment fails with a unique-constraint error. A teammate proposes updating the old row instead. What do you do?

Product tags are stored as a comma-separated text column, and a teammate suggests a PostgreSQL text array instead. When is a list in one column acceptable, and when must it become a junction table? Use the queries the shop runs to decide.

An online store has 8,000 categories up to six levels deep. Every product page shows a breadcrumb, every category page lists the products of its whole subtree, and merchandisers move a few branches each week. Which tree model do you choose, and how do you keep it correct?

A discussion forum stores each thread's comments as nested sets. On threads with 20,000 comments, posting a reply takes over a second, and at peak replies fail with lock timeouts. Explain why, and how you would move the forum to another model without downtime.

A document app checks on every request whether a file lies inside any folder shared with the user. Folders nest up to 20 levels, a user can see thousands of shared folders, and folders are moved now and then. Design the tables and the check query, and say what a folder move costs.