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 код

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.