Database Design Interview Questions for SQL

Show answer

A primary key uniquely identifies each row in a table and cannot be NULL or duplicate, such as student_id. A foreign key is a column in one table that references the primary key of another, such as orders.customer_id referencing customers.customer_id. Foreign keys enforce referential integrity, preventing orders for customers that do not exist.

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

Show answer

Both enforce uniqueness. A table can have only one primary key, and its columns cannot contain NULL. A table can have many unique constraints, and in most databases a unique column can hold NULL, often more than one, though SQL Server allows only one NULL by default. Primary keys often create a clustered index in SQL Server; unique keys create non-clustered indexes.

What is normalisation and why is it needed?

Show answer

Normalisation organises data into tables to reduce redundancy and avoid insert, update and delete anomalies. For example, storing customer address in every order row means updating it in many places. By separating customers and orders and linking with keys, each fact is stored once. Common forms are 1NF, 2NF, 3NF and BCNF, and most transactional systems target 3NF.

Explain 1NF, 2NF and 3NF with simple examples.

Show answer

1NF: each column holds atomic values and there are no repeating groups, so no comma-separated phone numbers. 2NF: in 1NF and every non-key column depends on the whole composite key, so product name should not sit in an order_items table keyed by order and product. 3NF: in 2NF with no transitive dependency, so city should not determine state inside the employees table.

Design tables for students, courses and enrolments.

Show answer

CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL ); CREATE TABLE courses ( course_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL ); CREATE TABLE enrolments ( student_id INT REFERENCES students(student_id), course_id INT REFERENCES courses(course_id), enrolled_on DATE, PRIMARY KEY (student_id, course_id) ); The junction table resolves the many-to-many relationship.

What is a composite key?

Show answer

A composite key is a primary key made of two or more columns that together uniquely identify a row. For example, in an enrolments table, student_id and course_id together form the key because a student can join many courses and a course has many students, but each pair appears once. Column order in a composite key matters for index usage.

How do you document a database you designed so others can use it?

Show answer

Situation: I built a reporting schema that three new analysts needed to use. Task: help them work independently. Action: I created an ER diagram, wrote a data dictionary with table purpose, grain, keys and column meanings, added comments in the database, and shared sample queries for common questions. Result: new analysts started writing correct queries in their first week with very few doubts.

What are constraints in SQL?

Show answer

Constraints enforce rules on data: PRIMARY KEY for unique row identity, FOREIGN KEY for valid references, UNIQUE for no duplicates, NOT NULL for required values, CHECK for conditions like age >= 18, and DEFAULT for automatic values. They protect data quality at the database level, so bad data is rejected even if an application has a bug.

What is denormalisation and when is it used?

Show answer

Denormalisation intentionally adds redundancy, such as storing customer name in an orders reporting table, to reduce joins and speed up reads. It is common in data warehouses, reporting tables and high-read applications. The cost is extra storage and the need to keep copies consistent through ETL, triggers or application logic. Use it when read performance matters more than write simplicity.

What is the difference between OLTP and OLAP databases?

Show answer

OLTP systems handle many short transactions such as orders and payments, with normalised schemas, row-based storage and focus on fast inserts and updates. OLAP systems support analysis over large history, using denormalised star or snowflake schemas, columnar storage and heavy aggregations. A bank's core system is OLTP, while its data warehouse used for monthly reports is OLAP.

What is a star schema and a snowflake schema?

Show answer

A star schema has a central fact table with measures such as sales amount and foreign keys to denormalised dimension tables like date, product, store and customer. A snowflake schema normalises dimensions further, such as product to category to department. Star schemas are simpler and faster for BI queries, while snowflake schemas save space and reduce redundancy.

What are surrogate keys and natural keys?

Show answer

A natural key is a real-world identifier like PAN number, email or product code. A surrogate key is a system-generated value, usually an integer or UUID, with no business meaning. Surrogate keys are stable, compact and good for joins, while natural keys can change or be long. A common design uses a surrogate primary key plus a unique constraint on the natural key.

What are referential actions such as ON DELETE CASCADE?

Show answer

Referential actions define what happens to child rows when a parent row is deleted or updated. CASCADE deletes or updates children automatically, SET NULL sets the foreign key to NULL, SET DEFAULT uses the default value, and RESTRICT or NO ACTION blocks the change. CASCADE is convenient for dependent data like order items but risky on important data, because one delete can remove many rows.

Add a foreign key and a check constraint to an existing table.

Show answer

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id); ALTER TABLE orders ADD CONSTRAINT chk_amount_positive CHECK (amount > 0); First clean existing rows that violate the rule, otherwise the ALTER fails. Naming constraints makes errors easier to read.

A table stores phone numbers as 'ph1, ph2, ph3' in one column. What is wrong and how do you fix it?

Show answer

The column violates first normal form because it stores multiple values in one field, making search, validation and updates difficult. Create a separate customer_phones table with customer_id, phone and type columns, one row per number, with a foreign key to customers. Migrate data by splitting the strings, then drop the old column after applications are updated.

How would you design a database for an online food delivery app at a high level?

Show answer

Core tables are users, addresses, restaurants, menu_items, orders, order_items, payments, delivery_partners and deliveries. Orders reference users, restaurants and addresses, order_items reference orders and menu_items with price at order time, and payments and deliveries reference orders. Add status history tables for tracking and indexes on user_id, restaurant_id and created_at for common queries.

A reporting team wants to query the production OLTP database directly. What are the risks and alternatives?

Show answer

Heavy analytical queries can lock rows, use CPU and slow customer transactions, and normalised schemas make reporting complex. Alternatives are a read replica for reporting, a data warehouse loaded by ETL or CDC with a star schema, or materialised summary tables. Grant read-only access with limits if direct access is unavoidable, and schedule heavy jobs off-peak.

Tell me about a database design decision you made and its outcome.

Show answer

Situation: our app stored user preferences in a wide table with 60 nullable columns. Task: make it flexible for new settings. Action: I proposed a key-value preferences table with a JSON column for rarely used settings, kept frequently queried fields as columns, and wrote a migration. Result: new settings no longer needed schema changes and query speed for common fields stayed the same.

What are slowly changing dimensions in data warehousing?

Show answer

Slowly changing dimensions track changes in dimension attributes over time. Type 1 overwrites old values with no history. Type 2 adds a new row with effective dates and a current flag, preserving full history, such as a customer moving city. Type 3 keeps a previous value column for limited history. Type 2 is most common when reports must reflect attributes as they were at transaction time.

What is BCNF and how is it different from 3NF?

Show answer

Boyce-Codd Normal Form requires that for every functional dependency, the determinant is a candidate key. 3NF allows a dependency where the dependent attribute is part of a candidate key, while BCNF does not. For example, if a teacher teaches only one subject but a student-subject pair determines the teacher, the table is in 3NF but not BCNF and should be split.

Product prices change often, but old orders must keep the price at the time of purchase. How do you design it?

Show answer

Store the unit price, discount and tax in order_items when the order is created instead of reading the current product price, so history is not affected by updates. Maintain a product_price_history table with effective_from and effective_to dates for analysis. Reports on past orders use order_items values, while catalogue pages use the current price.

Your team debates using UUIDs or auto-increment integers as primary keys. What do you advise?

Show answer

Auto-increment integers are compact, fast to index and naturally ordered, but expose counts and are hard to generate across distributed systems. UUIDs are globally unique and good for distributed inserts and merging data, but larger and random, causing index fragmentation. Time-ordered UUIDs such as UUIDv7 reduce fragmentation. Choose based on scale, distribution and security needs.

How do you handle disagreement with developers about schema changes?

Show answer

Situation: a developer wanted to drop foreign keys to speed up inserts. Task: protect data integrity without blocking him. Action: I measured insert performance with and without constraints, showed the overhead was under five percent, and suggested batch inserts and proper indexes instead. Result: we kept the constraints, insert speed improved through batching, and orphan records stayed at zero.

An e-commerce app needs products with very different attributes, such as phones and shirts. How do you model them?