7 questions foundWhat is database normalization, and why is it an important concept in schema design?
Beginner Database normalization is the process of organizing tables and columns to reduce data duplication and improve data integrity, typically by breaking down a large table into smaller, related tables, which helps prevent inconsistencies that can occur when the same piece of information is stored in multiple places and only some of those copies get updated.
-- Unnormalized: customer info repeated in every order row
-- Normalized: customer info stored once in a separate table
CREATE TABLE Customers (CustomerId INT PRIMARY KEY, Name VARCHAR(100), Email VARCHAR(100));
CREATE TABLE Orders (OrderId INT PRIMARY KEY, CustomerId INT, Amount DECIMAL(10,2));
Real-world example A company redesigns its orders table to reference a separate customers table instead of repeating the customer's name and email on every single order, ensuring a customer's information only needs to be updated in one place.
Common follow-ups: What problems can arise from an unnormalized database design?;Does normalization always improve query performance?
Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)
What is the first normal form, and what specific rule does it require a table to follow?
Beginner The first normal form requires that every column in a table contains only a single, indivisible value, meaning you should not store multiple values, like several phone numbers, crammed into a single column separated by commas, but instead store them properly across multiple rows or in a separate related table.
-- Violates first normal form
CREATE TABLE Customers (CustomerId INT, PhoneNumbers VARCHAR(200)); -- '555-1234, 555-5678'
-- Follows first normal form
CREATE TABLE CustomerPhones (CustomerId INT, PhoneNumber VARCHAR(20));
Real-world example A customer database originally stored multiple phone numbers as a single comma separated text value, but was redesigned into a separate phone numbers table to properly follow the first normal form and make searching by phone number possible.
Common follow-ups: Why is storing multiple values in a single column problematic for searching and filtering?;How do you fix a table that already violates first normal form?
Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)
What is the second normal form, and how does it build on the requirements of the first normal form?
Intermediate The second normal form requires a table to already be in first normal form, and additionally requires that every non key column depends on the entire primary key, not just part of it, which specifically matters for tables with a composite primary key made up of more than one column.
-- Violates second normal form: ProductName only depends on ProductId, not OrderId
CREATE TABLE OrderItems (OrderId INT, ProductId INT, ProductName VARCHAR(100), Quantity INT);
-- Fixed: ProductName moved to its own table
CREATE TABLE Products (ProductId INT PRIMARY KEY, ProductName VARCHAR(100));
CREATE TABLE OrderItems (OrderId INT, ProductId INT, Quantity INT);
Real-world example An order items table originally stored the product name directly alongside the composite order and product id key, but was redesigned to move the product name into its own dedicated products table, properly satisfying the second normal form.
Common follow-ups: Why does the second normal form only apply to tables with a composite primary key?;What problems occur if a table violates second normal form?
Constraints (Primary Key Foreign Key Check & Unique);Data Types & Schema Design
What is the third normal form, and what kind of dependency does it specifically eliminate?
Intermediate The third normal form requires a table to already satisfy second normal form, and additionally requires that non key columns do not depend on other non key columns, eliminating what is called a transitive dependency, where one column's value depends on another column that is not itself the primary key.
-- Violates third normal form: City depends on ZipCode, not directly on CustomerId
CREATE TABLE Customers (CustomerId INT PRIMARY KEY, ZipCode VARCHAR(10), City VARCHAR(50));
-- Fixed: City moved to a separate zip codes table
CREATE TABLE ZipCodes (ZipCode VARCHAR(10) PRIMARY KEY, City VARCHAR(50));
CREATE TABLE Customers (CustomerId INT PRIMARY KEY, ZipCode VARCHAR(10));
Real-world example A customer database originally stored both a zip code and its corresponding city directly on the customer table, but was redesigned to move the city information into a separate zip codes table, removing the transitive dependency and properly reaching third normal form.
Common follow-ups: How do you identify a transitive dependency in an existing table design?;Is achieving third normal form typically considered sufficient for most practical applications?
Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)
When would it make sense to intentionally denormalize a database design, and what specific tradeoffs come with that decision?
Advanced You might intentionally denormalize by duplicating some data to significantly speed up frequently run, performance critical read queries, particularly in reporting or data warehouse scenarios, accepting the tradeoff that the duplicated data now needs to be carefully kept in sync, and that write operations become more complex since they may need to update the same information in multiple places.
-- Denormalized for reporting speed: order total pre-calculated and stored
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
TotalAmount DECIMAL(10,2) -- calculated and stored, not recalculated on every read
);
Real-world example A reporting heavy e-commerce platform intentionally stores a pre-calculated order total directly on the orders table, denormalizing slightly to avoid recalculating that total from individual line items on every single report execution.
Common follow-ups: How do you keep denormalized data properly synchronized as the underlying source data changes?;What is the difference between denormalizing for performance and simply having a poorly designed schema?
Query Optimization & Plans;Data Types & Schema Design
How would you approach normalizing an existing, poorly designed legacy database without breaking the applications that currently depend on it?
Advanced You would carefully analyze the current schema and its actual usage patterns, design the properly normalized target schema, migrate the data into the new structure, and often create views that mimic the old table structure temporarily, letting existing application code continue to function correctly while you gradually update it to work directly with the new, properly normalized tables.
-- A compatibility view can mimic the old denormalized structure
-- while the underlying tables have already been normalized
CREATE VIEW LegacyOrdersView AS
SELECT o.OrderId, c.Name AS CustomerName, o.Amount
FROM Orders o JOIN Customers c ON o.CustomerId = c.CustomerId;
Real-world example A company migrating a legacy application to a properly normalized database creates temporary compatibility views that mimic the old table structure, allowing the existing application to keep working correctly while the team gradually updates it to use the new schema directly.
Common follow-ups: What risks come with running a compatibility view long term instead of updating the application?;How do you thoroughly test that a normalization migration has not introduced any data integrity issues?
Views;Data Types & Schema Design
What practical problems, such as update anomalies, can occur in a poorly normalized database, and how does normalization address them?
Intermediate In a poorly normalized database, updating a single piece of information, like a customer's address stored in multiple rows across many orders, might require updating every one of those rows, and if even one is missed, the data becomes inconsistent, an issue called an update anomaly, which normalization solves by storing that address in exactly one place, referenced wherever it is needed.
-- Poorly normalized: updating a customer's address requires
-- updating it in every single order row for that customer
UPDATE Orders SET CustomerAddress = 'New Address' WHERE CustomerId = 123;
-- Properly normalized: update happens in exactly one place
UPDATE Customers SET Address = 'New Address' WHERE CustomerId = 123;
Real-world example A support team discovers a customer's shipping address is inconsistent across several of their past orders due to a poorly normalized design, prompting the team to redesign the schema so address information is stored and updated in exactly one place.
Common follow-ups: What other types of anomalies besides update anomalies can occur in poorly normalized databases?;How do you identify these kinds of anomalies in an existing schema before they cause real problems?
Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)