Shop Project - Documentation
Shop LDM
Part 04.1: Trading, Products & Sets
This module represents the trading structure for products and sets. It is an extension of the product module with a focus on trading collectible objects and related products.
The structure supports multilingual product information, different rarity levels, and the grouping of product variants into sets and set variants.
- Product: Contains all products (base entity).
- Category_product: Links categories with products (n:m).
- Set: Expansions / sets (e.g., booster sets).
- Set_variant: Variants of a set (e.g., 1st Edition, Unlimited).
- Lang_setvariant: Multilingual names for set variants.
- Product_info: Multilingual product information.
- Rarity: Defines rarity levels (Common, Rare, Secret Rare, etc.).
- Product_variant: Variants of a product (e.g., different editions).
- Set_variant_product_variant: Links set variants with product variants (n:m).
- Product_variant_info: Links product variants with multilingual information.
- v_product_full: View for products with topic and set information.
This module is the foundation for trading and managing collectible card products in the shop.
Product (Template)
Purpose: Serves as the foundation for product variants and information and together they serve as a template to quickly create items.
Fields:
- pro_ID (PK)
- cat_ID (FK → tb_category) -- Topic/Category Link
- pro_number VARCHAR(50) -- Product number
- pro_status CHAR(1) -- Status (A=Active, I=Inactive, D=Deleted)
Note: At least one product_info + one product_variant MUST exist.Category_product (Link)
Purpose: Enables flexible categorization of products. (n:n relationship).
Fields:
- pro_ID (PK → tb_product)
- cat_ID (PK → tb_category)Set (Grouping)
Purpose: Creates a product bundle such as a game set or a construction kit. While the set is usually sold as a whole in a traditional store, this system offers maximum flexibility: Each component can be sold individually.
Fields:
- set_ID (PK)
- cat_ID (FK → tb_category)
- cun_ID (FK → country)
- set_name VARCHAR(150) NOT NULL -- Set name
- set_code VARCHAR(50) UNIQUE -- Set code (e.g., 'LOB', 'MRD')
- set_year INT -- 1st release year of the set
- set_status CHAR(1) NOT NULL DEFAULT 'A' -- Status
Indexes:
- idx_set_year (set_year)
- idx_set_cat_year (cat_ID, set_year)
Note: Automatically populated from set variants (trigger).Set_variant (Production Variant)
Purpose: Contains variants of a set (e.g., European Edition).
Fields:
- stv_ID (PK)
- set_ID (FK → tb_set)
- stv_name VARCHAR(150)
- stv_count INT -- Number of objects
- stv_release DATE -- Release date
- stv_type VARCHAR(50) -- Variant type
- stv_pro_types JSON -- Product types as JSON
- stv_status CHAR(1)
Indexes:
- idx_stv_release (stv_release)
Note: On insert, tb_set.set_year is automatically updated.trg_set_year_from_variant Trigger
Purpose: Automatically maintains tb_set.set_year from stv_release on insert of a set variant.
Trigger: AFTER INSERT ON tb_set_variant
Logic:
- If stv_release is NULL → no update
- If YEAR(stv_release) <= 0 → no update
- If set_year is NULL → set to year value
- If YEAR(stv_release) < set_year → set to earlier year
- Otherwise → set_year remains unchangedLang_setvariant (Multilingual)
Purpose: Links languages with set variants for multilingual names (optional).
Fields:
- lan_ID (PK → tb_language)
- stv_ID (PK → tb_set_variant)
- stv_code VARCHAR(50) -- Language-specific code
- stv_name VARCHAR(150) -- Language-specific nameProduct_info (Language Variant)
Fields:
- pin_ID (PK)
- pro_ID (FK → tb_product)
- lan_ID (FK → tb_language)
- pin_name VARCHAR(150) NOT NULL -- Product name
- pin_description TEXT -- Product description
- pin_priority INT NOT NULL DEFAULT 0 -- Priority
- pin_status CHAR(1) NOT NULL -- Status
- UNIQUE KEY unique_product_language (pro_ID, lan_ID)Rarity (Rarity Levels)
Felder:
- rar_ID (PK)
- cat_ID (FK → tb_category)
- rar_level INT NOT NULL -- Numeric rarity level (1-10)
- rar_key VARCHAR(50) UNIQUE NOT NULL -- Unique key
- rar_name VARCHAR(150) NOT NULL -- Name (always English)
- rar_status CHAR(1) NOT NULL DEFAULT 'A'Product_variant (Depending on Edition)
Purpose: Contains variants of a product (e.g., different editions).
Fields:
- var_ID (PK)
- pro_ID (FK → tb_product)
- cat_ID (FK → tb_category)
- rar_ID (FK → tb_rarity)
- var_number VARCHAR(50) NOT NULL -- Variant number
- var_image VARCHAR(255) NULL -- Image URL/filename
- var_priority INT NOT NULL DEFAULT 0 -- Priority (sorting)
- var_status CHAR(1) NOT NULL -- Status
Note: At least one product_info + one product_variant MUST exist.Svar_pvar (Link)
Purpose: Links set variants with product variants (n:n relationship).
Fields:
- stv_ID (PK → tb_set_variant)
- var_ID (PK → tb_product_variant)
- svp_status CHAR(1)Product_variant_info (Link)
Purpose: Links product variants with multilingual information.
Fields:
- pin_ID (PK → tb_product_info)
- var_ID (PK → tb_product_variant)
Index:
- idx_svarpvar_var (var_ID)v_product_full View
Purpose: Provides products with topic and set information.
Fields:
- pro_ID -- Product ID
- pro_number -- Product number
- pro_status -- Product status
- set_ID -- Set ID (if assigned)
- set_name -- Set name (if assigned)
- set_code -- Set code (if assigned)
Source:
- FROM tb_product p
- LEFT JOIN tb_product_variant pv ON p.pro_ID = pv.pro_ID
- LEFT JOIN tb_svar_pvar svp ON pv.var_ID = svp.var_ID
- LEFT JOIN tb_set_variant stv ON svp.stv_ID = stv.stv_ID
- LEFT JOIN tb_set s ON stv.set_ID = s.set_ID