DeployU
Interviews / Databases / What is database normalization and why is it important?

Questions

What is the difference between `INNER JOIN`, `LEFT JOIN`, and `RIGHT JOIN`?
Q C
What are window functions and how do you use them in PostgreSQL?
Q C
What are common table expressions (CTEs) and how do you use them in PostgreSQL?
Q C
What is the difference between `UNION` and `UNION ALL` in PostgreSQL?
Q C
Your database query is slow. How do you optimize it?
Q C
What are indexes and how do they work in PostgreSQL?
Q C
What are transactions and why are they useful in PostgreSQL?
Q C
How do you set up replication in PostgreSQL?
Q C
How do you do a backup and restore of a PostgreSQL database?
Q C
What is the `JSONB` data type and how do you use it in PostgreSQL?
Q C
How do you do full-text search in PostgreSQL?
Q C
What is database normalization and why is it important?
Q C

What is database normalization and why is it important?

conceptual Database Design Interactive Quiz Code Examples

The Scenario

You are a database administrator at a fintech company. You are designing a new database schema for a new service. You need to make sure that the database schema is well-designed and that it does not have any data redundancy.

The Challenge

Explain what database normalization is and why it is important. What are the different normal forms, and what are the trade-offs between them?

Wrong Approach

A junior engineer might not be aware of database normalization. They might just create a single table with all the data, which would lead to data redundancy and would be difficult to maintain.

Right Approach

A senior engineer would know that database normalization is a critical part of database design. They would be able to explain the different normal forms and would have a clear plan for how to design a well-normalized database schema.

Step 1: Understand What Database Normalization Is

Database normalization is the process of organizing the columns and tables of a relational database to minimize data redundancy.

Step 2: The Different Normal Forms

Normal FormDescription
1NFEach column must have a unique name, and all values in a column must be atomic.
2NFMust be in 1NF, and all non-key columns must be fully dependent on the primary key.
3NFMust be in 2NF, and all non-key columns must not be dependent on any other non-key columns.
BCNFA stricter version of 3NF.

Step 3: The Trade-offs of Normalization

ProsCons
Reduces data redundancyCan lead to more complex queries with more JOINs.
Improves data integrityCan be more difficult to design and implement.
Makes the database more flexible

When to Denormalize

In some cases, you might want to denormalize your database to improve performance. Denormalization is the process of intentionally adding redundant data to your database to avoid complex JOINs.

You should only denormalize your database if you have a good reason to do so, and you should be aware of the trade-offs.

Practice Question

You have a table with a column that contains a list of comma-separated values. Which normal form is this table not in?