Examples of Data Denormalization and Its Consequences

Normalization solves some problems, but in certain cases, it creates others—especially when it comes to performance. Today, we're gonna open the gates to the dark (and sometimes bright) art of denormalization. Yep, you can break the normalization rules... but only if you know what you're doing!

Denormalization is basically the opposite of normalization. If normalization splits tables into separate logical entities to minimize redundancy, denormalization brings data back together to boost performance. Denormalization is often used when, under heavy loads and frequent complex queries, joining a bunch of tables starts to slow the system down.

You could say denormalization is a trade-off between data purity and query speed.

When Should You Use Denormalization?

Like with any tool, it's important to know when denormalization makes sense. You'd use it in situations like these:

1. Frequently used queries are getting slow. When a high-load system keeps running the same queries (like summary reports and aggregates), joining a bunch of tables can take a lot of time. Denormalization helps cut down on those joins.

2. Analytics and stats tasks. In analytics systems (like BI—Business Intelligence), you often need to crunch a ton of data. In these cases, denormalization speeds things up by using "prepped" data.

3. Complex queries. If you have to join five, ten, or even more tables to get your data, that can really slow down your database. Denormalization lets you simplify your query structure.

4. The number of joins is just ridiculous. If your queries are joining 25 tables in a JOIN, maybe it's time to rethink your approach.

Denormalization Examples

Example 1: Online Store. In a normalized online store database, you might have tables like:

1. customers — customer data.
2. orders — order info.
3. products — product data.
4. order_items — items in an order.

A query to get info might look like this:

SELECT
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    c.customer_id = 42;

But what if our online store is handling hundreds of thousands of orders a day? This query will get way too slow because of all those joins.

Solution: denormalization.

Let's make a table for the info we use all the time:

CREATE TABLE order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_id,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id;

Now, when we need the data, we just query order_summary:

SELECT * FROM order_summary WHERE customer_id = 42;

Example 2: Analytics System. Let's say you're working with a database for a company that sells event tickets. There are tables:

1. events — event info.
2. sales — ticket sales data.

If analysts need to build a report on the average revenue per ticket for all events, the normalized structure makes you run an aggregate query every time:

SELECT
    e.event_name,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_name;

This query can be pretty slow, especially if each sale record is millions of rows.

Solution: denormalization. Let's make a separate table with aggregated data:

CREATE TABLE event_summary AS
SELECT 
    e.event_id,
    e.event_name,
    COUNT(s.sale_id) AS ticket_count,
    SUM(s.price) AS total_revenue,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_id, e.event_name;

Now reports will run faster at the aggregated level:

SELECT
    event_name, 
    avg_ticket_price 
FROM 
    event_summary;

Consequences of Denormalization

Denormalization can definitely speed up queries, but it's not a magic wand that'll fix everything. Here's what you might run into if you go down this road.

First—data duplication. When the same info is stored in multiple places, your database size grows fast, and it gets harder to manage.

Second—updating data is now trickier. Imagine you have customer data in the customers table, and also a copy in the order_summary table. If a customer changes their name or address, you gotta remember to update it in both places. Miss one, and boom—error, because your data doesn't match anymore.

Third—because of all this redundancy, it's easy to get confused and make mistakes. It's like having different versions of the same doc—sometimes it's tough to tell which one is right.

And finally, maintaining and evolving this kind of database is harder. You'll have to write special triggers or scripts to keep all the data copies in sync. That's extra work for devs.

So yeah, denormalization is a tool you should use wisely, knowing all the pros and cons.

2

Task

SQL SELF, level 25, lesson 4

Locked

Creating a denormalized table based on existing data

Creating a denormalized table based on existing data

Open

1

Survey/quiz

Data Normalization, level 25, lesson 4

Unavailable

Data Normalization

Data Normalization

Open