Shop Project - Documentation

language

Shop LDM

Part 08: Wishlists & Search Tracking

This module handles search queries, watchlists, and notifications.

It is the active monitoring layer of the shop: Users can save searches or products, and the system regularly checks whether new matches exist and notifications should be sent.

🗺️ Stage 3 | 📚 Current | 📟 2026 06 15 | 📍 Database - Wishlists & Search Tracking

  • Saved_search: Saved search queries of a user.
  • Cs_content: Links a saved search with categories.
  • Wantlist: Personal wish or watchlists.
  • Pw_content: Contents of the wantlist (watched products).
  • Index_want_search: Central indexing of all searches & watchlists.
  • Trigger_queue: Queue of actions to be checked.
  • Trigger_log: Log of trigger executions.
  • Alarm: Concrete notifications to users.

This module makes the shop proactive: Users don't have to constantly check themselves but receive targeted alarms about new articles or offers. This supports customer loyalty and significantly increases activity in the marketplace.

Saved_search

Purpose: Saved search queries of a user (as JSON or search parameters).

Fields:
    - sas_ID           (PK)
    - u_ID             INT                     -- FK → tb_user
    - sas_name         VARCHAR(100)            -- Search name
    - sas_value        TEXT                    -- JSON or search parameters

Note: Searches can also have attributes from part 3 (tb_user_attribut).

Cs_content (Link)

Purpose: Links a saved search with categories, including optional price threshold.

Fields:
    - sas_ID           (PK → saved_search)
    - cat_ID           (PK → category)
    - cs_threshold     DECIMAL(12,2)            -- Price threshold

Wantlist

Purpose: Personal wish or watchlists of users (with name and optional image).

Fields:
    - wtl_ID           (PK)
    - u_ID             (FK → user)
    - ast_ID           (FK → art_storage)      -- optional
    - wtl_name         VARCHAR(100)            -- List name
    - wtl_image        VARCHAR(255)            -- Image

tb_pw_content (Wishlist Contents)

Purpose: Contents of the wantlist – watched products with optional price limit.

Fields:
    - wtl_ID           (PK → wantlist)
    - pro_ID           (PK → product)
    - var_ID           (FK → product_variant)
    - lan_ID           (FK → language)
    - pw_threshold     DECIMAL(12,2)           -- Price limit

Note: A wantlist can also have attributes from part 3.

Index_want_search (Indexing)

Purpose: Central indexing of all searches & watchlists - including frequency and processing status.

Fields:
    - iws_ID            (PK)
    - wtl_ID            (FK → wantlist)
    - sas_ID            (FK → saved_search)
    - u_ID              (FK → user)
    - cat_ID            (FK → category)
    - cat_top_ID        (FK → category)        -- Top category for broader search
    - u_trigger_freq    INT                    -- Frequency (e.g., in hours/days)
    - iws_work_flag     CHAR(1)                -- Y/N, whether currently being processed
    - iws_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Note: The CHECK constraint for either wtl_ID or sas_ID is prepared in SQL but currently commented out due to MariaDB compatibility.

Trigger_queue

Purpose: Queue of search or watchlist actions to be checked (with status & schedule).

Fields:
    - trq_ID            (PK)
    - iws_ID            (FK → index_want_search)
    - trq_status        CHAR(1)                -- P=Pending, R=Running, C=Completed, F=Failed
    - trq_scheduled_at  DATETIME               -- Scheduled execution time

Trigger_log

Purpose: Log of trigger execution - whether successful, failed, or with warning.

Fields:
    - trl_ID            (PK)
    - trq_ID            (FK → trigger_queue)
    - iws_ID            (FK → index_want_search)
    - trl_status        CHAR(1)                -- S=Success, E=Error, W=Warning
    - trl_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Alarm (Notifications)

Purpose: Concrete notifications to users when new articles match the criteria.

Fields:
    - ala_ID            (PK)
    - trl_ID            (FK → trigger_log)
    - u_ID              (FK → user)
    - art_ID            (FK → article)
    - ala_status        CHAR(1)                -- O=Open, S=Sent, A=Acknowledged, C=Closed
    - ala_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP
    - ala_send_time     DATETIME               -- Time of sending

Note: Status enables tracking of notification states.