Complete overview of customer orders

Description

This view provides a complete overview of customer orders by combining information about the customer, order, and products included in the order.

The view is intended for:

• displaying customer orders in the user interface
• order management
• customer order tracking
• sales reports and analytical queries

Tables covered by the view:

• Client
• Order
• Product
• makes_order
• includes

SQL код

CREATE OR REPLACE VIEW vw_customer_order_overview AS 
SELECT 
    o.order_num, 
    o.quantity, 
    o.status, 
    o.last_modified_date, 
    o.payment_method, 
    o.discount, 
    c.client_id, 
    c.first_name, 
    c.last_name, 
    c.email, 
    p.code AS product_code, 
    p.description AS product_description, 
    p.price FROM "order" o 
JOIN makes_order mo 
    ON mo.order_num = o.order_num 
JOIN client c 
    ON c.client_id = mo.client_id 
JOIN includes i 
    ON i.order_num = o.order_num 
JOIN product p 
    ON p.code = i.code;

Logic explanation

1. The basic order information is retrieved from order.

2. The makes_order relationship connects each order with the client who made it.

3. Client information is retrieved from client.

4. The includes relationship connects orders with the products they contain.

5. Product information is retrieved from product.

6. The result provides customer, order, and product information in a single view.

7. An order containing multiple products can appear in multiple rows, one for each included product.

Reason for view

This view is useful because:

• It combines all important information about customer orders
• It simplifies order management queries
• It reduces the complexity of repeated JOIN operations
• It provides a consistent format for displaying order information
• It can be used for customer order history and sales analysis

Without this view, every application query requiring customer, order, and product information would need to independently join client, makes_order, order, includes, and product.