Automatic calculation of store rating from order reviews

Description

This trigger automatically calculates and updates the rating of a store whenever a customer review is added, modified, or deleted.

The store rating is calculated as the average of all ratings given to orders associated with that store.

This ensures that the rating attribute in the STORE table always reflects the current customer reviews without requiring the application to manually recalculate it.

Tables involved

• Store
• Order
• Review
• for_store

Type of trigger

• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

Activated on the review table.

SQL Code

CREATE OR REPLACE FUNCTION update_store_rating()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_num INT;
    v_store_id INT;
BEGIN
    -- Determine which order was affected
    IF TG_OP = 'DELETE' THEN
        v_order_num := OLD.order_num;
    ELSE
        v_order_num := NEW.order_num;
    END IF;

    -- Find the store associated with the order
    SELECT fs.store_id
    INTO v_store_id
    FROM for_store fs
    JOIN "order" o
        ON o.order_num = v_order_num
    WHERE fs.request_num IS NOT NULL
    LIMIT 1;

    -- Update the store rating
    IF v_store_id IS NOT NULL THEN
        UPDATE store s
        SET rating = (
            SELECT COALESCE(AVG(r.rating), 0)
            FROM review r
            JOIN "order" o
                ON o.order_num = r.order_num
            -- The exact relationship between orders and stores
            -- should be used here according to the final schema
            WHERE o.order_num IN (
                SELECT i.order_num
                FROM includes i
                JOIN sells sl
                    ON sl.code = i.code
                WHERE sl.store_id = v_store_id
            )
        )
        WHERE s.store_id = v_store_id;
    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_store_rating
AFTER INSERT OR UPDATE OR DELETE
ON review
FOR EACH ROW
EXECUTE FUNCTION update_store_rating();

Logic explanation

Whenever a review is inserted, updated, or deleted, the trigger:

1. Identifies the affected order_num. 2. Determines which store is associated with the order. 3. Finds all reviews belonging to orders associated with that store. 4. Calculates the average review rating using AVG(). 5. Updates the rating attribute of the corresponding STORE.

COALESCE is used so that a store with no reviews receives a rating of 0 instead of NULL.

Reason for trigger

This trigger is useful because:

• Rating is a derived value.
• The application should not have to manually recalculate store ratings.
• Ratings remain consistent after reviews are inserted, modified, or removed.
• It prevents outdated ratings from remaining in the STORE table.