Customer request and response overview

Description

This view provides a complete overview of customer requests, including the customer who submitted the request and the personnel member who answered it.

The view is intended for:

• customer support management
• tracking customer requests
• monitoring response activity
• analyzing customer satisfaction

Tables covered by the view:

• Client
• Request
• Personal
• makes_request
• answers

SQL код

CREATE OR REPLACE VIEW vw_customer_request_response AS
SELECT
    r.request_num,
    r.date_and_time,
    r.problem,
    r.notes_of_communication,
    r.customer_satisfaction,

    c.client_id,
    c.first_name AS client_first_name,
    c.last_name AS client_last_name,
    c.email AS client_email,

    p.id AS employee_id,
    p.first_name AS employee_first_name,
    p.last_name AS employee_last_name

FROM request r
JOIN make_request mr
    ON mr.request_num = r.request_num
JOIN client c
    ON c.client_id = mr.client_id
LEFT JOIN answers a
    ON a.request_num = r.request_num
LEFT JOIN personal p
    ON p.id = a.id;

Logic explanation

1. The request table provides the request details.

2. make_request connects each request with the client who submitted it.

3. client provides customer information.

4. answers connects requests with personnel members who answered them.

5. personal provides information about the employee.

6. LEFT JOIN is used for answers because a request may not have been answered yet.

Reason for view

This view is useful because:

• It combines customer and request information
• It shows which employee handled a request
• It supports customer-service monitoring
• It makes unanswered requests easier to identify
• It simplifies customer satisfaction analysis

Without this view, the application would need to repeatedly join request, make_request, client, answers, and personal whenever customer request information is required.