Changes between Initial Version and Version 1 of View5

Timestamp:

08/21/26 08:53:10 (26 hours ago)

Author:

235018

Comment:

--

View5

@@ +1 @@
+= 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 код
+{{{#!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.