How Normalization Enhances Database Design
Q: Describe a scenario where you successfully improved the database design through normalization.
- Database Design and Normalisation
- Mid level question
Explore all the latest Database Design and Normalisation interview questions and answers
ExploreMost Recent & up-to date
100% Actual interview focused
Create Database Design and Normalisation interview for FREE!
In my previous role as a Database Administrator for an e-commerce company, I encountered a scenario where the existing database design was leading to data redundancy and integrity issues. The database initially had a single table named "Orders" that included fields for Order ID, Customer Name, Customer Address, Product ID, Product Name, Quantity, and Price.
Upon reviewing the design, I noticed that customer information and product details were being repeated for every order. For example, if a customer placed multiple orders, their name and address were stored again with each order entry, leading to redundancy. Additionally, product details were duplicated, which made it challenging to maintain or update information consistently.
To address this, I proposed normalizing the database to at least the third normal form (3NF). I created separate tables for Customers, Products, and Orders.
1. The Customers table consisted of Customer ID, Customer Name, and Customer Address.
2. The Products table contained Product ID, Product Name, and Price.
3. The Orders table maintained Order ID, Customer ID (as a foreign key), Product ID (as a foreign key), Quantity, and order date.
This structure eliminated redundancy by ensuring that customer and product information was only stored once. By having separate tables and employing foreign key relationships, we ensured data integrity; if a customer’s address changed, it only needed to be updated in the Customers table instead of multiple rows in the Orders table.
After implementing these changes, we noticed that the performance of the database improved significantly. Query times were reduced as the database engine could operate on smaller, more focused tables. Furthermore, data integrity issues decreased, leading to a more reliable system. This experience reinforced the importance of normalization in effective database design.
Upon reviewing the design, I noticed that customer information and product details were being repeated for every order. For example, if a customer placed multiple orders, their name and address were stored again with each order entry, leading to redundancy. Additionally, product details were duplicated, which made it challenging to maintain or update information consistently.
To address this, I proposed normalizing the database to at least the third normal form (3NF). I created separate tables for Customers, Products, and Orders.
1. The Customers table consisted of Customer ID, Customer Name, and Customer Address.
2. The Products table contained Product ID, Product Name, and Price.
3. The Orders table maintained Order ID, Customer ID (as a foreign key), Product ID (as a foreign key), Quantity, and order date.
This structure eliminated redundancy by ensuring that customer and product information was only stored once. By having separate tables and employing foreign key relationships, we ensured data integrity; if a customer’s address changed, it only needed to be updated in the Customers table instead of multiple rows in the Orders table.
After implementing these changes, we noticed that the performance of the database improved significantly. Query times were reduced as the database engine could operate on smaller, more focused tables. Furthermore, data integrity issues decreased, leading to a more reliable system. This experience reinforced the importance of normalization in effective database design.