Why we use Denormalization? Explain with real time example.

Denormalization is used in database design to improve performance by reintroducing redundancy into a normalized database schema. Let's consider a real-time example to illustrate the benefits of denormalization.

Example: E-commerce Website

Suppose you are designing a database for an e-commerce website that sells products. You have two main entities: "Products" and "Orders". The normalized database schema might look like this:

    Products Table:
        product_id (primary key)
        product_name
        price
        category_id (foreign key)

    Orders Table:
        order_id (primary key)
        customer_id (foreign key)
        order_date
        total_amount

    Order_Items Table:
        order_item_id (primary key)
        order_id (foreign key)
        product_id (foreign key)
        quantity
        price

In this normalized schema, the order items are stored in a separate table with references to the product and order tables using foreign keys.

Now, let's consider a scenario where you need to display a list of orders with their associated products and details on the website. Without denormalization, achieving this might require complex joins across multiple tables, which can impact performance, especially when dealing with a large number of orders and order items.

To optimize query performance, you can denormalize the schema by adding redundant columns. For example, you can add the product name and price columns to the Order_Items table:

    Order_Items Table (Denormalized):
        order_item_id (primary key)
        order_id (foreign key)
        product_id (foreign key)
        quantity
        price
        product_name (redundant column)
        product_price (redundant column)

With this denormalized schema, retrieving order details along with product information becomes much simpler and more efficient. You can now fetch the order details and display them on the website without the need for complex joins or additional queries to retrieve product information.

By denormalizing the schema in this scenario, you have improved query performance, reduced the complexity of the queries, and provided a better user experience on the e-commerce website. However, it's important to note that denormalization introduces redundancy, so you must carefully manage updates and ensure consistency between redundant data.

This example demonstrates how denormalization can be used to optimize performance and simplify queries in real-world scenarios where retrieving related data is a common operation.