Store inventory overview

Description

This view provides an overview of the current inventory of every store, including the products sold, their prices, available quantities, discounts, and product information.

The view is intended for:

• inventory management
• monitoring product availability
• store management
• identifying products with low stock

Tables covered by the view:

• Product
• Store
• sells

SQL код

CREATE OR REPLACE VIEW vw_store_inventory AS
SELECT
    s.store_id,
    s.name AS store_name,

    p.code AS product_code,
    p.description,
    p.price,
    p.availability,
    p.approx_production_time,

    sl.quantity,
    sl.discount

FROM store s
JOIN sells sl
    ON sl.store_id = s.store_id
JOIN product p
    ON p.code = sl.code;

Logic explanation

1. The store table provides store information.

2. The sells relationship connects stores with the products they sell.

3. The product table provides product details.

4. The quantity and discount are retrieved from sells.

5. Each row represents a product being sold by a particular store.

Reason for view

This view is useful because:

• It provides a centralized inventory overview
• It allows stores to monitor available products
• It simplifies stock-related queries
• It allows products and their quantities to be easily compared between stores
• It can be used to identify products with low availability

Without this view, inventory queries would repeatedly need to join store, sells, and product, increasing the complexity of the application.