Shop Project - Documentation

language

Shop LDM

Part 02: Users and Rights

This module manages the user and rights management of the shop.

It distinguishes between private and business customers as well as internal roles (e.g., admin, support, moderator).

Additionally, it includes an audit and validation system to make changes in the shop traceable and provide volunteers with a reward system.

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

  • User_role: Roles of the users linked with rights.
  • User_status: Status of the users in the system.
  • User: The specific user in the system.
  • User_attribute: Attributes of the users in the system.
  • Address: Addresses of the users in the system.
  • Target: Targets a specific table for a user action.
  • Action: Defines the action a user performs on a target table.
  • Manager_rights: Links a user with a target and an action and defines the rights.
  • Manager_log: Logs all changes of a user on a target with an action.
  • Review: Enables the evaluation of changes by other users.
  • Validation: Validates the changes through reviews and provides feedback to the user.
  • Reward_log: Logs all rewards for a user based on their actions and their evaluations.

This module provides the foundation for security, moderation, and community management. It ensures that all changes in the system are traceable while simultaneously creating positive incentives for participation.

User_role (User Roles)

Purpose: Defines the various roles in the system.

Fields:
    - rol_ID       (PK)
    - rol_key      CHAR(1) UNIQUE                  -- Role abbreviation (e.g., 'A' for admin)
    - rol_name     VARCHAR(50) UNIQUE              -- Role name (for developers)

Note: Display is translated based on the key.

User_status (User Status)

Purpose: Manages user status (including deletion status).

Fields:
    - ust_ID       (PK)
    - ust_key      CHAR(1) UNIQUE                  -- Status abbreviation
                                                   -- A = Active
                                                   -- N = Not confirmed
                                                   -- D = Deleted
                                                   -- R = Renew password
    - ust_name     VARCHAR(100) UNIQUE             -- Status name (for developers)

User (User)

Purpose: Central user management with all core information.

Fields:
    - u_ID              (PK)
    - rol_ID            (FK → user_role.rol_ID) DEFAULT 6 -- Role (Default: User)
    - ust_ID            (FK → user_status.ust_ID) DEFAULT 2 -- Status (Default: Not confirmed)
    - u_name            VARCHAR(50) UNIQUE             -- Username (unique)
    - u_avatar          VARCHAR(255)                   -- Profile picture (filename with extension)
    - u_fname           VARCHAR(50)                    -- First name
    - u_lname           VARCHAR(50)                    -- Last name
    - u_mail            VARCHAR(100) NOT NULL          -- Email
    - u_phone           VARCHAR(20)                    -- Phone number
    - u_password        VARCHAR(255) NOT NULL          -- Password (hashed)
    - u_trust_level     INT DEFAULT 0                  -- Trust level (for contributors)
    - u_open_fees       DECIMAL(10,2) DEFAULT 0.00     -- Outstanding fees
    - u_reward_point    INT DEFAULT 0                  -- Reward points for volunteers
    - u_trigger_freq    INT DEFAULT 10                 -- Maximum triggers per round

Indexes:
    - idx_user_status (ust_ID)
    - idx_user_role (rol_ID)
    - idx_user_role_status (rol_ID, ust_ID)

Note: Private and business customers are distinguished by role.

User_attribut (User Attributes)

Purpose: Extendable table for additional attributes like temporary tokens or other information.

Fields:
    - uat_ID       (PK)
    - u_ID         (FK → user.u_ID)
    - uat_key      VARCHAR(100)                        -- Attribute key (e.g., 'token', 'companyname')
    - uat_value    TEXT                                -- Can store multiple values separated by a delimiter
    - UNIQUE (u_ID, uat_key)

Note: Can be enhanced later with a lookup table attribute_type.

Address (Addresses)

Purpose: Stores user address information.

Fields:
    - adr_ID            (PK)
    - cun_ID            (FK → country.cun_ID)          -- Linked in shipping_tb
    - u_ID              (FK → user.u_ID)
    - adr_name          VARCHAR(100)                   -- Address name (e.g., 'Home', 'Office')
    - adr_line_option   VARCHAR(255)                   -- Additional line
    - adr_street        VARCHAR(255) NOT NULL          -- Street
    - adr_hous_num      VARCHAR(10) NOT NULL           -- House number
    - adr_zipcode       VARCHAR(10) NOT NULL           -- Postal code
    - adr_locality      VARCHAR(255) NOT NULL          -- City/Locality
    - adr_type          CHAR(1) NOT NULL               -- M = Main address, B = Billing address, D = Delivery address

Target (Target Tables)

Purpose: Defines which entity (table) a user may access.

Fields:
    - tar_ID         (PK)
    - tar_tb_name    VARCHAR(50)                       -- Table name (e.g., 'tb_category', 'tb_product')

Action (Actions)

Purpose: Defines which action a user may perform.

Fields:
    - act_ID            (PK)
    - act_name          VARCHAR(50)                    -- Action name (e.g., 'create', 'update', 'delete')
    - act_reward_value  INT DEFAULT 0                  -- Reward points for this action

Manager_rights (Rights Management)

Purpose: Manages user rights at entry or table level with temporal validity.

Fields:
    - mgr_ID            (PK)
    - u_ID              (FK → user.u_ID)
    - tar_ID            (FK → target.tar_ID)           -- Area
    - tar_tb_ID         INT NULL                       -- Optional concrete entry ID
    - mgr_right_level   INT NOT NULL                   -- 0=Viewer, 1=Editor, 2=Moderator, 3=Admin
    - mgr_trust_level   INT DEFAULT 0                  -- Dynamically calculated
    - mgr_valid_from    DATETIME NOT NULL              -- Valid from
    - mgr_valid_to      DATETIME DEFAULT NULL          -- Valid to (NULL = unlimited, not yet defined)

Manager_log (Change Log)

Purpose: Complete logging of changes including before/after values.

Fields:
    - mgl_ID            (PK)
    - u_ID              (FK → user.u_ID)               -- Who performed the action
    - tar_ID            (FK → target.tar_ID)           -- Which target
    - tar_tb_ID         INT NULL                       -- Optional concrete entry ID
    - act_ID            (FK → action.act_ID)           -- Which action
    - old_value         TEXT                           -- Previous state (optional)
    - new_value         TEXT                           -- New state (optional)
    - mgl_details       TEXT                           -- JSON with additional info (e.g., metadata)
    - mgl_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Review (Reviews)

Purpose: Reviews of changes for quality assurance.

Fields:
    - rev_ID            (PK)
    - mgl_ID            (FK → manager_log.mgl_ID)   	-- Which log entry
    - u_ID              (FK → user.u_ID)               -- Who is reviewing
    - rev_point         INT                            -- Review score, e.g., 1–10
    - rev_comment       TEXT                           -- Optional justification

Note: Reviews must not be rewarded as this creates wrong incentives.

Validation (Changes)

Purpose: Validation status of changes.

Fields:
    - val_ID            (PK)
    - rev_ID            (FK → review.rev_ID)           -- Which review influenced
    - u_ID              (FK → user.u_ID)               -- Who validates
    - mgl_ID            (FK → manager_log.mgl_ID)      -- Which action is validated
    - val_status        CHAR(1)                        -- O=Open, P=Pending, V=Validated, E=Exam, R=Rejected
    - val_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Reward_log (Traceability)

Purpose: Reward system for volunteers who make valid and successful contributions.

Fields:
    - rel_ID            (PK)
    - mgl_ID            (FK → tb_manager_log)          -- Which action is rewarded
    - u_ID              (FK → tb_user)                 -- Who is rewarded
    - val_ID            (FK → tb_validation)           -- Which validation influences the reward
    - act_ID            (FK → tb_action)               -- Which action is rewarded

Note: Only those who have successfully contributed are rewarded.