What is normalization, and when would you choose to denormalize?

What the interviewer wants to hear

• Normalization removes duplicate data by splitting into related tables
• Normal forms such as 1NF, 2NF, 3NF
• Prevents update, insert and delete anomalies
• Denormalization adds controlled duplication for read speed
• Typical for reporting, analytics or very hot read paths

Model answer

Normalization means organising tables so each fact is stored in one place. For example, a customer's city lives in the customers table, not copied into every order. Following the normal forms, usually up to third normal form, prevents anomalies: you cannot update a city in one row and forget the others. For most transactional (OLTP) systems I start normalized. I denormalize only when there is a measured performance problem, for example a dashboard that joins six tables on every page load, or for analytics and reporting tables. Then I add controlled duplication, such as a summary table or materialized view, and make sure there is a clear way to keep it in sync, like triggers, scheduled refreshes or application logic.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• What is database replication, and what is the difference between synchronous and asynchronous replication?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.