Today we're gonna take another, deeper look at an important topic: the pros and cons of normalization, so you can get a better grip on how these concepts work in real life. So, buckle up — here we go!
Eliminating Data Redundancy
When your table isn't normalized, you might notice the same info duplicated in different rows. For example, in an orders table, the customer's address might repeat for every order. Normalization gets rid of this duplication by moving shared data into separate tables.
Example:
-- Before normalization
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_name TEXT,
customer_address TEXT,
order_date DATE
);
-- After normalization
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
customer_name TEXT,
customer_address TEXT
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE
);Why does this matter? Less duplicated data means fewer chances for mistakes. If a customer changes their address, you only need to update it in one place.
Ensuring Data Integrity
When data is split into logical tables, it's easier to manage their relationships. Using foreign keys helps keep your data consistent automatically. For example, you can't accidentally delete a customer who's referenced in the orders table.
Example:
-- ON DELETE CASCADE makes sure deleting a customer also deletes related orders.
-- Without this option PostgreSQL defaults to NO ACTION:
-- deleting a customer referenced by orders will simply not be allowed.
ALTER TABLE orders
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;
You can be sure your database structure won't allow "orphaned" data to appear.
Easier Updates
When your database is normalized, updates are easy and less error-prone. Back to the customer example — if a customer changes their address, you update their record in just one table. In non-normalized tables, you might forget to update the address in some rows, which leads to inconsistent data.
Minimizing Anomalies
Insert anomalies: In a non-normalized table, you might run into situations where you can't add a record without extra info. For example, you can't add an order until you know the customer's name.
Update anomalies: Errors can happen when updating data. For example, if you change a customer's name in one row, it might stay old in another row.
Delete anomalies: Deleting a record might cause you to lose important info. For example, deleting an order also deletes the customer's name if that data is stored in the same table.
Reducing Data Size
Normalization often shrinks the size of your database since it removes duplicated data. That's a big deal when you're storing large amounts of info.
Cons of Normalization
1. Complex Database Structure
Over time, normalization can lead to a complex database structure with thousands(!) of related tables. In these cases, to get data that used to be in one table, you have to write complicated SQL queries with lots of JOINs.
Example of complexity:
-- Query to get order info with product and category details
SELECT
o.order_id,
o.order_date,
c.customer_name,
p.product_name,
cat.category_name,
oi.quantity,
oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id;
If the number of tables and relationships grows, query execution time can really go up.
2. Performance Drops with Frequent Joins
Joining tables (JOIN) a lot can be resource-heavy, especially if the tables are big and don't have indexes. In analytics systems where queries need to process millions of rows, normalization can cause serious performance hits.
3. Need for Extra Denormalization Steps
If a normalized database structure is used for analytics, you often have to temporarily denormalize it to speed up analytical queries. This might mean creating VIEWs or tables for aggregating data.
Example:
-- Denormalized view for analytics
CREATE VIEW orders_with_customers AS
SELECT
o.order_id,
o.order_date,
c.customer_name,
c.customer_address
FROM
orders o
JOIN
customers c ON o.customer_id = c.customer_id;
4. High Entry Barrier
For newbies, normalization can seem tough. Instead of one table with all the data, you have to work with several tables and figure out their relationships. This can slow down development, especially if the team isn't super experienced with databases.
5. Sometimes Redundancy Is Actually Good
In real projects, sometimes it's better to keep redundant data for better performance. For example, if your app often uses data that you can only get by doing a complicated join, it's better to store it in one table.
GO TO FULL VERSION