Changes between Initial Version and Version 1 of View1

Timestamp:

08/21/26 08:31:01 (27 hours ago)

Author:

235018

Comment:

--

View1

@@ +1 @@
+= Complete overview of products and stores
+
+=== Description
+This view provides a complete overview of the products currently being sold by each store, combining product information with store information and sales data in a single view.
+
+The view is intended for:
+
+- displaying products and stores in the user interface
+- inventory management
+- product availability checks
+- analytical queries and reports
+
+==== Tables covered by the view:
+
+- Product
+- Store
+- sells
+
+==== SQL код
+{{{#!sql
+CREATE OR REPLACE VIEW vw_product_store_overview AS 
+SELECT 
+    p.code AS product_code, 
+    p.description, 
+    p.price, 
+    p.availability, 
+    p.weight, 
+    p.approx_production_time, 
+    s.store_id, 
+    s.name AS store_name, 
+    s.physical_address, 
+    s.rating, 
+    sl.quantity, 
+    sl.discount 
+FROM product p 
+JOIN sells sl 
+    ON sl.code = p.code 
+JOIN store s 
+   ON s.store_id = sl.store_id;
+
+}}}
+
+==== Logic explanation
+**1.** The basic product information is retrieved from `product`.
+**2.** The `sells` relationship connects each product with the stores selling it.
+**3.** Store information is retrieved from `store`.
+**4.** The current quantity and possible discount are displayed from `sells`.
+**5.** A similar product can appear multiple times if it is sold by multiple stores.
+
+==== Reason for view 
+This view is useful because:
+
+- It centralizes product and store information in one place
+- It simplifies inventory-related queries
+- It reduces the need to repeatedly implement the same JOIN operations
+- It provides a consistent format for displaying products and stores
+- It can be used directly by the application for product listings and inventory management
+
+Without this view, every application query that needs both product and store information would have to independently join product, sells, and store.