Shop Project - Documentation

language

Shop LDM

Part 01: Labels

This module manages the multilingual capabilities and text blocks of the shop.

Instead of storing texts directly in the code, label keys are defined, which contain language-specific translations.

🗺️ Stage 0 | 📚 Current | 📟 2026 06 15 | 📍 Database - labels

  • language: Contains the supported languages (e.g., German, English, French).
  • label_key: Contains unique keys for text elements (e.g., buttons, error messages).
  • label: Contains the specific translation of a label_key per language.
  • text: Contains the specific translation of texts.
  • page: Defines shop pages like checkout, profile, homepage.
  • lp_content: Links label keys to specific pages if they are not globally applicable.

It allows the shop interface to be dynamically output in any language and texts to be adjusted at any time without changing the code.

Language (Languages)

Purpose: Base table for supported languages.

Fields:
    - lan_ID      (PK)
    - lan_key     VARCHAR(5) UNIQUE        -- Language code (e.g., 'DE', 'EN')
    - lan_name    VARCHAR(50)              -- Display name (e.g., 'Deutsch', 'English', 'Français')

Label Key (Label Keys)

Purpose: Unique identifier for texts. Supports grouping and global applicability.

Fields:
    - lky_ID      (PK)
    - lky_key     VARCHAR(50) UNIQUE       -- Key name for a label
    - lky_group   VARCHAR(200) 		   -- Grouping (e.g., 'nav', 'btn')
    - lky_is_global BOOLEAN                -- 1 = global (page-independent), 0 = page-specific

Trigger: global or page-specific
Note: It is recommended to use prefixes for the key

Label (Short Texts)

Purpose: Stores translations for UI labels (e.g., buttons, menus).

Fields:
    - lab_ID      (PK)
    - lan_ID      (FK → language.lan_ID)
    - lky_ID      (FK → label_key.lky_ID)
    - lab_value   TEXT                     -- e.g., 'In den Warenkorb', 'Add to cart'
    - UNIQUE(lky_ID, lan_ID)               -- one label key per language only

Text (Long Texts)

Purpose: Similar to tb_label, but designed for long-form content (LONGTEXT) such as descriptions.

Fields:
    - tex_ID      (PK)
    - lan_ID      (FK → language.lan_ID)
    - lky_ID      (FK → label_key.lky_ID)  -- I use the rest of the label structure here
    - tex_value   LONGTEXT                 -- e.g., T&Cs paragraphs, help text
    - UNIQUE (lky_ID, lan_ID)              -- one label key per language only

Page (Page Definitions)

Purpose: Defines the various pages of the web application. Must be identical to the global page constants.

Fields:
    - pag_ID      (PK)
    - pag_key     VARCHAR(100) UNIQUE      -- unique key for the page (e.g., 'checkout', 'profile')

lp_content (Link)

Purpose: Connects LabelKeys to specific pages if they are not global.

Fields:
    - lky_ID      (PK, FK → label_key.lky_ID)
    - pag_ID      (PK, FK → page.pag_ID)

Note: Only for label_key without global

trg_labelkey_global_check Trigger

Trigger: Before inserting into tb_lp_content
Process:
   1. Check if the LabelKey is global
   2. If global, prevent insertion

Output:
    - Error message if a global LabelKey is inserted

vw_label_export View

Purpose: Consolidated view for label keys with their translations and associated pages.

Fields:
    - lky_ID      LabelKey
    - lky_key     LabelKey                 -- Key name for a label
    - lky_group   LabelKey      		    -- Grouping (e.g., 'nav', 'btn')
    - lky_is_global LabelKey               -- 1 = global, 0 = page-specific
    - lan_ID      Language
    - pag_IDs     Page                     -- Comma-separated list of all associated page IDs
    - label_value Label                    -- Final text value after fallback logic

vw_pages View

Purpose: Consolidated view for pages with their language-specific names.

Fields:
    - lan_ID      Language
    - pag_ID      Page
    - pag_key     Page                     -- Unique key of the page
    - pag_name    Page                     -- Language-specific name of the page or pag_key as fallback