Shop Project - Documentation

language

Shop LDM

Part 04: Categories & Articles

This module manages the category and article management of the shop.

Here, many connections, attributes, and images of the articles are also managed with a focus on the marketplace.

🗺️ Stage 0 | 📚 Current | 📟 2026 06 15 | 📍 Database - Categories & Articles

  • Category: Hierarchical node category system with node type, tree structure, and page linkage.
  • Cat_hierarchy: Allows a category to have multiple parents or children.
  • Condition: Condition definitions per category with JSON translations and optional icon functionality.
  • Packaging: Packaging units for automatic shipping cost calculation.
  • Article: The actual trade item that is sold or listed. Can be directly linked to a product or only to a category (e.g., for classifieds).
  • Art_image: Manages images of the articles.
  • Art_storage & art_manager: Additional structures for storage locations and quantity/reservation management.
  • Attribute & att_content: Flexible way to assign additional features (e.g., size, material, technical data) to products, variants, or articles.

There is a second part for the products. Here, the basics are created and the necessary groundwork for the marketplace is laid.

Category (Categories)

Purpose: Orchestrates what belongs where and how

Fields:
    - cat_ID        (PK)
    - pag_ID        (FK → page.pag_ID, NULL)
    - cat_key       VARCHAR(100)
    - cat_node_type VARCHAR(100)                   -- N = normal, S = specialisation, L = list type and more
    - cat_level     INT NOT NULL                   -- 0 = Maincategory, 1 = Subcategory …
    - cat_tree_num  INT NOT NULL,                  -- Primary tree (System tree)
    - cat_status    CHAR(1)                        -- C=Created, O=On_check, A=Active

Category Hierarchy (Categories)

Purpose: Defines the hierarchy (n:n Mapping) and connection type of categories

Fields:
    - parent_cat_ID (PK → category.cat\_ID, NULL)
    - child_cat_ID  (PK → category.cat\_ID, NULL)
    - relation_type ENUM('tree', 'ref', 'special') NOT NULL
    - rel_tree_num  INT NOT NULL
    - sort_order    INT NOT NULL

INSERT Trigger: Synchronization of tb_category.cat_key with tb_page.pag_key
UPDATE Trigger: Synchronization of tb_category.cat_key with tb_page.pag_key

tr_sync_cat_key_insert Trigger

Trigger: Before inserting into tb_category
Process:
   1. Synchronizes the category key with the page key if applicable

Output:

tr_sync_cat_key_update Trigger

Trigger: Before updating in tb_category
Process:
   1. Synchronizes the category key with the page key if applicable

Output:

Condition (States)

Fields:
    - con_ID        (PK)
    - cat_ID        (FK → category.cat\_ID)        -- allows specialized state lists per category
    - con_key       VARCHAR(100)                   -- for comparisons and internal system use
    - con_name      JSON NOT NULL                  -- for direct translation, prevents label overload
                                                      Format: {"DE":"Neu", "EN":"New"}
    - con_icon      VARCHAR(50)                    -- Name of the PHP function (e.g., place_newIcon)
    - con_status    CHAR(1)                        -- C=Created, O=On_check, A=Active, I=Inactive

Packaging (Packaging Units)

Purpose: Defined Packaging units for shipping.

Fields:
    - pac_ID        (PK)
    - pac_name      VARCHAR(100)
    - pac_weight    DECIMAL(12)
    - pac_in_length DECIMAL(12)
    - pac_in_width  DECIMAL(12)
    - pac_in_height DECIMAL(12)
    - pac_out_length DECIMAL(12)
    - pac_out_width DECIMAL(12)
    - pac_out_height DECIMAL(12)
    - pac_status    CHAR(1)                        -- C=Created, O=On\_check, A=Active, I=Inactive

Article (Sales Object)

Purpose: Defined Sales Objects.

Fields:
    - art_ID        (PK)
    - u_ID          (FK → user.u_ID)
    - var_ID        (FK → product_variant.var_ID, NULL)
    - cat_ID        (FK → category.cat_ID, NULL)
    - pac_ID        (FK → packaging.pac_ID, NULL)
    - con_ID        (FK → condition.con_ID)
    - art_name      VARCHAR(250)
    - art_description TEXT
    - art_quantity  INT
    - art_open_quan INT
    - art_price     DECIMAL(12,2)
    - art_host_status CHAR(1)                        -- C=Created, O=On Check, A=Active, I=Inactive, B=Blocked

Note: The exclusive check between var\_ID and cat\_ID is prepared in SQL, but currently commented out.
Note: Sales status is defined by the Article Manager, not to be confused with the host status.

Art_image (Marketplace)

Purpose: Images for classifieds or additional images.

Fields:
    - img_ID        (PK)
    - art_ID        (FK → article.art_ID)
    - img_name      VARCHAR(255)
    - img_priority  INT                -- 0 = Main image, 1+ = Gallery
    - img_status    CHAR(1)            -- 'A' = Active, 'O' = On Check (Virus scan/Moderation)

Art_storage (Storage)

Purpose: Defines storage locations for articles.

Fields:
    - ast_ID        (PK)
    - u_ID          (FK → user.u_ID)
    - ast_name      VARCHAR(100)       -- e.g., 'Main Warehouse', 'Set Box 12'
    - ast_is_a_set  CHAR(1)            -- Y/N: Treated as a set/collection
    - ast_location  VARCHAR(100)
    - ast_description TEXT NULL        -- optional: additional information (e.g., location description)

Art_manager (Fulfillment)

Purpose: Links articles with storage locations, orders, and available quantities.

Fields:
    - ama_ID        (PK)
    - art_ID        (FK → article.art_ID)
    - ast_ID        (FK → art_storage.ast_ID, NULL)  -- Storage location or set assignment
    - pur_ID        (FK → purchase.pur_ID, NULL)     -- If part of an order, otherwise NULL
    - ama_quantity  INT
    - ama_status    CHAR(1)                      -- R=Reserved, P=Private, S=Sold, C=Cancelled, A=Active
    - ama_stor_locat VARCHAR(100) NULL        -- e.g., shelf number, compartment, drawer
Trigger: SUM(ama_quantity) <= article.art_quantity
    -- Ensure that the total quantity in art_manager does not exceed the available quantity in the article

tr_sync_cat_key_insert Trigger

Trigger: Before inserting into tb_art_manager
Process:
   1. Checks the total quantity in tb_art_manager for the corresponding article

Output:
   - Error message if the total quantity exceeds the available quantity

Attributes (general)

Fields:
    - att_ID        (PK)
    - att_name      VARCHAR(100)
    - att_type      ENUM ('numeric', 'string', 'text', 'bool', 'size')
    - att_status    CHAR(1)                      -- C=Created, O=On_check, A=Active, I=Inactive

Att_content (Attribute-Target Tables)

Purpose:   Stores attribute values for various target tables

Fields:
    - atc_ID        (PK)
    - att_ID        (FK → attribute.att_ID)
    - cat_ID        (FK → category.cat_ID)
    - art_ID        (FK → article.art_ID)
    - pro_ID        (FK → product.pro_ID)
    - pin_ID        (FK → product_info.pin_ID)
    - var_ID        (FK → product_variant.var_ID)
    - atc_value_n   DECIMAL(12,4) NULL
    - atc_value_n2  DECIMAL(12,4) NULL
    - atc_value_n3  DECIMAL(12,4) NULL
    - atc_value_s   VARCHAR(255) NULL
    - atc_value_t   TEXT NULL
    - atc_value_b   BOOLEAN NULL
Trigger: exactly one Entity-FK must be set

trg_attcontent_single_entity Trigger

Trigger: Before inserting into tb_att_content
Process:
   1. Checks that exactly one Entity-ID is set

Output:
   - Error message if not exactly one Entity-ID is set

Views Category Navigation & Articles

v_navigation_base View

Purpose: Combines categories with page keys and hierarchy data for PHP tree construction.

Fields:
    - cat_ID      Category
    - pag_ID      Page
    - cat_node_type Category                   -- Node type
    - cat_type    Category                     -- Category type
    - cat_key     Category                     -- Unique key of the category
    - cat_level   Category                     -- Level of the category
    - cat_tree_num Category                    -- Tree number
    - cat_status  Category                     -- Status of the category
    - cat_P_ID    Category                     -- ID of the parent category
    - cat_r_type  Category                     -- Relationship type
    - cat_sort    Category                     -- Sort order

Note: This view is built in two parts. The first part contains all actual hierarchy links (Trees, Refs, Specials), 
      while the second part represents all Level-0 categories as independent 'roots'. This ensures that 'Level 0' categories that were not referenced also appear as 'roots'.

v_node_children View

Purpose: Provides a flat list of all child relationships based on the tb_cat_hierarchy

Fields:
    - parent_cat_ID ID of the parent category
    - child_id      ID of the child category
    - child_key     Key of the child category
    - child_pag_id  Page ID of the child category (NULL if no page assigned)
    - cat_node_type Node type of the child category
    - cat_type      Category type of the child category
    - relation_type Relationship type (tree, ref, special)
    - sort_order    Sort order of children under the same parent

v_category_path View

Purpose: Provides the parent path of a category (for inheritance)

Fields:
    - start_cat_ID  ID of the starting category
    - current_cat_ID ID of the current category in the path
    - depth         Depth in the path (0 = starting category, 1 = parent, ...)

Note: This view uses a recursive CTE to determine all parent categories of a given category. Only relationships of type 'tree' are considered to reflect the actual hierarchy.

v_category_condition View

Purpose: Provides conditions of the first valid category level

Fields:
    - cat_ID    ID of the category
    - con_ID    ID of the condition
    - con_name  Name of the condition
    - con_icon  Icon of the condition
    - con_status Status of the condition

Note: This view provides the conditions of the first valid category level for each category. 
         It first checks if the category itself has conditions. If not, it finds the nearest parent category with conditions and returns those conditions. 
         This allows for a kind of inheritance of conditions along the category hierarchy.

v_cat_descendants View

Purpose: Provides all child categories (tree + ref) recursively for each category

Fields:
    - start_cat_ID  ID of the starting category
    - current_cat_ID ID of the current child category
    - depth         Depth of the relationship (0 = starting category, 1 = direct children, ...)

Note: This view uses a recursive CTE to determine all child categories of a given category. 
      It considers relationships of type 'tree' and 'ref' to include both actual hierarchy children and reference children.

v_article_by_category View

Purpose: Provides all articles of a category including children + main image + conditions

Fields:
    - cat_ID            ID of the category
    - found_cat_ID      ID of the category where the article was found (can differ from cat_ID if article is in a child category)
    - art_ID            ID of the article
    - u_ID              ID of the user (seller)
    - art_name          Name of the article
    - art_open_quan     Available quantity of the article
    - art_price         Price of the article
    - art_host_status   Host status of the article (C=Created, O=On Check, A=Active, I=Inactive, B=Blocked)
    - con_name          JSON with translated names of the condition (e.g., {"DE": "Neu", "EN": "New"})
    - con_icon          Name of the PHP function for the condition icon (e.g., getConditionIcon)
    - art_main_image    Filename of the main image (img_priority = 0) or NULL if no image available
    - art_main_img_status Status of the main image (C=Created, O=On Check, A=Active, I=Inactive) or NULL

Note: This view is based on v_cat_descendants and provides all articles that are located directly in the category or in any subcategory.
       It also provides information about the condition and main image of the article to facilitate display in the gallery.