Normalization & Database Design

1. What is Normalization and why is it used?

Normalization is the process of organizing tables to reduce duplicate data and keep the database consistent. Each piece of data is stored once and referenced elsewhere through keys.

The problem normalization solves: Imagine an Orders table that repeats the customer’s full name and address on every order.

If the customer moves, you have to update every single order row. Miss one row and the data becomes inconsistent.

Normalization splits the data into two tables. Customer details live once in a Customers table. Orders only store a customer ID that points back to it.

Before normalization (bad design):

order_id	customer_name	customer_address	product
1	Ali	Street 1	Pen
2	Ali	Street 1	Book

The address is repeated. If Ali moves, both rows must change.

After normalization (good design):

Customers table:

customer_id	name	address
1	Ali	Street 1

Orders table:

order_id	customer_id	product
1	1	Pen
2	1	Book

The address is stored once. A change touches only one row.

The normal forms:

• 1NF — every column holds a single value, and every row is unique.
• 2NF — 1NF plus every non-key column depends on the whole primary key.
• 3NF — 2NF plus no non-key column depends on another non-key column.

Key takeaway: Normalization keeps data consistent, shrinks storage, and makes updates safe. The trade-off is more tables and more joins, which is why read-heavy systems sometimes denormalize on purpose.

2. What is Denormalization, and when is it appropriate to use?

Denormalization is the intentional addition of duplicate data to a database. It trades storage and consistency for faster reads, by reducing the number of joins.

The problem with fully normalized data: A normalized database splits data into many small tables linked by keys.

Reading that data often requires joining several tables together.

On a huge, read-heavy system, those joins get slow.

How denormalization helps: Instead of joining, you store the already-combined data in one place.

Example — before and after:

Normalized: to show an order with the customer name, you join Orders with Customers.

Denormalized: you add the customer name directly into the Orders table.

order_id	customer_name	product
1	Ali	Pen
2	Ali	Book

Reading is now a single table scan, no join.

The costs:

• The customer name is duplicated across rows.
• If Ali changes her name, every row must be updated.
• You risk inconsistency if an update is missed.

When to use it:

• When reads vastly outnumber writes.
• When reporting queries join many tables.
• When the joins are the bottleneck.

Key takeaway: Normalize for data integrity. Denormalize for read speed. Use it deliberately on read-heavy reporting systems, not everywhere.

3. What is the difference between OLTP and OLAP systems?

OLTP handles daily, real-time transactions. OLAP handles complex analysis over large amounts of historical data.

OLTP — Online Transaction Processing: This is what a bank or online store uses every second.

Each operation is small and fast, like inserting an order or updating a balance.

The workload is write-heavy.

A single mistake matters, so consistency is critical.

OLAP — Online Analytical Processing: This is what a business analyst uses to answer questions like “what were sales by region last year?”

Each query is big and reads millions of rows.

The workload is read-heavy.

Speed of a single transaction doesn’t matter; the analysis result does.

Key differences table:

	OLTP	OLAP
Full form	Online Transaction Processing	Online Analytical Processing
Purpose	Day-to-day operations	Analysis and reporting
Workload	Many small writes	Large reads
Data	Current, up to date	Historical, accumulated
Example	Banking, shopping carts	Sales reports, trends

Key takeaway: OLTP keeps the business running. OLAP helps understand the business. The same company usually runs both on different databases.

4. Explain the difference between Star Schema and Snowflake Schema.

Both are ways to organize tables in a data warehouse. A star schema uses denormalized dimension tables. A snowflake schema uses normalized dimension tables.

The core idea: A data warehouse has a central fact table (the numbers, like sales) and surrounding dimension tables (the descriptions, like product and date).

Star Schema: The dimension tables are denormalized. All the details are in one table.

It looks like a star: a center with points around it.

Fewer joins, faster queries.

Snowflake Schema: The dimension tables are normalized. They are split into multiple related tables.

It looks like a snowflake: branches off branches.

Less duplication, but more joins.

Example — the Product dimension:

Star schema — one table:

product_id	product_name	brand	category	supplier
1	Pen	Bic	Stationery	Co A

Snowflake schema — split into three tables:

Products:

product_id	product_name	brand_id	category_id
1	Pen	1	1

Brands:

brand_id	brand_name	supplier
1	Bic	Co A

Categories:

category_id	category_name
1	Stationery

Key differences table:

	Star Schema	Snowflake Schema
Dimension tables	Denormalized	Normalized
Duplication	More	Less
Joins needed	Fewer	More
Query speed	Faster	Slower
Storage	More	Less

Key takeaway: Use a star schema for fast, simple queries. Use a snowflake schema when you want to reduce redundancy and storage.

5. What is a Surrogate Key, and why is it used?

A surrogate key is an artificial, system-generated identifier. It’s used when there’s no good natural key.

Natural key vs surrogate key: A natural key is a real-world value, like email or phone number.

The problem: natural keys can change. A person changes their email, or two people share a phone number.

A surrogate key is just a number the database creates, like 1, 2, 3.

It never changes and never repeats.

Example:

CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  phone VARCHAR(20)
);

Here customer_id is the surrogate key.

email could be a natural key, but it can change, so it’s not safe as the primary key.

Why surrogate keys are safer:

• They never change over time.
• They’re simple numbers, fast to index.
• The real data (like email) can change without breaking references.

Key differences table:

	Natural key	Surrogate key
Based on	Real-world data	System-generated
Can change	Yes	No
Example	Email, SSN	Auto-increment number
Safe as a primary key	Not always	Yes

Key takeaway: Use a surrogate key when the natural key is unstable, like an email that users can change. It keeps all references stable.

6. What is Database Sharding?

Sharding splits a large database horizontally across multiple servers. Each part is called a shard.

Why shard? One server can only hold so much data and handle so many queries.

When a single database can’t keep up, you split it.

How it works: Rows of the same table are distributed across shards.

Each shard stores a subset of the rows.

Example: A users table with 10 million rows split across 5 servers:

• Shard 1: users 1–2,000,000
• Shard 2: users 2,000,001–4,000,000
• and so on

How rows are assigned: Usually by a key, like user ID.

A simple rule: shard = user_id % 5.

User 7 goes to shard 2, user 12 goes to shard 2 as well (7%5=2, 12%5=2).

The trade-offs:

• Queries that need data across shards (like joins or global searches) become hard.
• If one shard fails, part of the data is unavailable.

Key differences table (vs vertical scaling):

	Sharding (horizontal)	Bigger server (vertical)
Adds	More servers	More CPU/RAM on one
Scale limit	Very high	Hardware limit
Cost	Complex to manage	Simple but expensive

Key takeaway: Sharding is a powerful scaling technique for very large data. It spreads load across machines, but makes cross-shard queries harder.

7. What is Database Normalization?

Normalization organizes data to reduce redundancy and improve data integrity. It splits large tables into smaller, related ones.

The problem it solves: Repeating the same data everywhere causes errors and wasted space.

Example — bad design:

order_id	customer_name	product
1	Ali	Pen
2	Ali	Book

The name is repeated. Change it once and you must change both rows.

Normalized design: Customers table:

customer_id	name
1	Ali

Orders table:

order_id	customer_id	product
1	1	Pen
2	1	Book

The name is stored once, referenced by ID.

The normal forms:

• 1NF — single values per column, unique rows.
• 2NF — 1NF plus every column depends on the whole key.
• 3NF — 2NF plus no column depends on another non-key column.

Key takeaway: Normalization removes duplication and keeps data consistent. The cost is more tables and more joins.