Shop Project - Documentation

Shop LDM

Part 03: User Views & Procedures

This module extends the user module with views and procedures that encapsulate complex queries and business logic.

Views simplify frequently used SELECT queries, while procedures provide complex operations and business logic securely and reusably.

🗺️ Stage 0 | 📚 Current | 📟 2026 06 15 | 📍 Database - User Views & Procedures

v_user: Returns users with role and status.
util_user_reference: Controls which tables block hard delete.
p_check_can_delete_user: Checks if a user can be physically deleted.
p_delete_user_associated_data: Cleans up personal or temporary data.
p_clear_user: GDPR-compliant anonymization of a user.
p_delete_user: Central main procedure for removing a user.

This module is continuously expanded according to the shop's requirements.

v_user View

Purpose: Returns users with associated role and status.

Fields:
    - u.*                -- All user fields from tb_user
    - u_role (rol_key)   -- Role abbreviation (e.g., 'A', 'U')
    - u_role_name        -- Role name (for developers)
    - u_status (ust_key) -- Status abbreviation (A, N, D, R)
    - u_status_name      -- Status name (for developers)

Source:
    - FROM tb_user u
    - JOIN tb_user_role r ON r.rol_ID = u.rol_ID
    - JOIN tb_user_status s ON s.ust_ID = u.ust_ID

Sorting: ORDER BY u.u_ID

Util_user_reference (Reference Helper Table)

Purpose: Controls which tables block hard delete and which data is cleaned up on deletion.

Fields:
    - urc_ID              (PK)
    - urc_table_name      VARCHAR(64) NOT NULL     -- Table to check or delete from
    - urc_column_name     VARCHAR(64) NOT NULL     -- Column pointing to the user
    - urc_is_blocking     BOOLEAN DEFAULT TRUE     -- TRUE = Hard delete forbidden
    - urc_is_child        BOOLEAN DEFAULT FALSE    -- TRUE = indirect via intermediate table
    - urc_to_table        VARCHAR(64) DEFAULT NULL -- Parent table (for indirect relationships)
    - urc_to_column       VARCHAR(64) DEFAULT NULL -- Column in parent

Constraints:
    - UNIQUE (urc_table_name, urc_column_name)
    - CHECK (Child references are only deletable reference data)
    - CHECK (Child must have parent specified)

p_check_can_delete_user Procedure

Purpose: Checks whether a user can be physically deleted (hard delete) or whether references in business-critical tables prevent this.

Input:
    - p_user_id INT

Process:
    1. Scans tb_util_user_reference for entries with urc_is_blocking = TRUE
    2. Checks for each table using p_check_has_one whether records exist

Output:
    - p_can_delete BOOLEAN (TRUE | FALSE)
    - p_reason VARCHAR(128) ('BLOCKED: tb_purchase.u_ID_buy' | 'OK')

p_delete_user_associated_data Procedure

Purpose: Cleans the system of personal or temporary data that does not need to be retained upon deletion or anonymization.

Input:
    - p_user_id INT

Process:
    1. Uses cursor on tb_util_user_reference (urc_is_blocking = FALSE)
    2. Deletes data in correct order (first is_child, then parent)
    3. Avoids foreign key errors

Affected tables (excerpt):
    - tb_alarm, tb_trigger_log, tb_trigger_queue
    - tb_index_want_search, tb_wantlist, tb_pw_content
    - tb_saved_search, tb_cs_content, tb_manager_rights
    - tb_reward_log, tb_article, tb_art_storage
    - tb_address, tb_user_attribut

p_clear_user Procedure

Purpose: GDPR-compliant anonymization of a user if hard delete is not possible.

Input:
    - p_user_id INT
    - p_deleted_by INT (Admin ID or user ID themselves)
    - p_reason ENUM('self', 'admin', 'inactive', 'dsgvo')

Process:
    1. Sets ust_ID to status 'D' (Deleted)
    2. Overwrites personal data:
       - u_name → '__u[ID]_deleted'
       - u_mail → '__u[ID].deleted@brulsim.ch'
       - u_phone, u_fname, u_lname, u_avatar → NULL
       - u_password → anonymous hash (SHA2)
    3. Stores deletion metadata in tb_user_attribut:
       - u_deleted_at (timestamp)
       - u_deleted_reason (reason)
       - u_deleted_by (who deleted)

p_delete_user Procedure

Purpose: The central main procedure for removing a user from the active system.

Input:
    - p_user_id INT
    - p_deleted_by INT
    - p_reason ENUM('self', 'admin', 'inactive', 'dsgvo')

Process:
    1. Validates that the user exists
    2. Calls p_check_can_delete_user (check deletability)
    3. Calls p_delete_user_associated_data (clean personal data)
    4. Decision:
       - If v_can_delete = TRUE → DELETE FROM tb_user (Hard Delete)
       - If v_can_delete = FALSE → CALL p_clear_user (Anonymization)

Error:
    - Signal SQLSTATE '45000' if user does not exist