Shop Project - Documentation

language

Shop LDM

Part 06: Purchase & Order Processing

This module represents the core process of purchases and orders in the shop.

It includes purchase processing, payments, refunds, communication between buyer and seller, as well as support tickets.

🗺️ Stage 1 | 📚 Current | 📟 2026 06 15 | 📍 Database - Purchase & Order Processing

  • Purchase: Central transaction table between buyer and seller.
  • Refunded: Management of refunds or additional payments.
  • Purchase_log: Log of all actions in an order's lifecycle.
  • Evaluation_buyer & Evaluation_seller: Mutual evaluations.
  • Conversation & Message: Communication structure for chats.
  • Ticket & Support: Support system with escalation levels.

This module connects transaction logic, communication, and support to ensure transparent and secure purchase processing.

Purchase (Purchase/Order)

Purpose: Contains all purchases/orders between buyer and seller.

Fields:
    - pur_ID           (PK)
    - u_ID_buy         (FK → user)             -- Buyer
    - u_ID_sell        (FK → user)             -- Seller
    - pac_ID           (FK → packaging)        -- Packaging unit
    - sip_ID           (FK → shipping_tariff)  -- Shipping method - from part 07
    - fes_ID           (FK → fees)             -- Fee model - from part 07
    - pur_adr_from     TEXT                    -- Sender address (string with separator)
    - pur_adr_to       TEXT                    -- Recipient address (string with separator)
    - pur_send_numb    VARCHAR(100)            -- Tracking number
    - pur_ref_numb     VARCHAR(50)             -- Seller reference number
    - pur_t_goods      DECIMAL(10,2)           -- Total price of goods
    - pur_t_fees       DECIMAL(10,2)           -- Fees
    - pur_t_shipping   DECIMAL(10,2)           -- Shipping costs
    - pur_total        DECIMAL(10,2)           -- Total amount
    - pur_status       CHAR(1)                 -- O=open, P=paid, S=shipped, D=done, C=cancelled

Note: Foreign key must be set in art_manager from 05 product.
Note: sip_ID and fes_ID are set in the SQL process in part 07 (Shipping).

Refunded (Refunds/Additional Payments)

Fields:
    - ref_ID           (PK)
    - u_ID             (FK → user)             -- Determines booking direction
    - pur_ID           (FK → purchase)
    - ref_value        DECIMAL(10,2)           -- Amount
    - ref_reason       TEXT                    -- Reason (optional)
    - ref_timestamp    DATETIME DEFAULT CURRENT_TIMESTAMP

Purchase_log (Order Log)

Purpose: Logs all actions in an order's lifecycle.

Fields:
    - pog_ID           (PK)
    - pur_ID           (FK → purchase)
    - u_ID             (FK → user)             -- Who triggered it
    - pog_action       VARCHAR(100)            -- Action (C=create, P=pay, S=ship, ...)
    - pog_timestamp    DATETIME DEFAULT CURRENT_TIMESTAMP

Evaluation_buyer

Fields:
    - evb_ID            (PK)
    - pur_ID            (FK → purchase)
    - evb_level_pack    INT                    -- 1-10, Packaging
    - evb_level_goods   INT                    -- 1-10, Goods
    - evb_level         INT                    -- 1-10, Overall
    - evb_comment       TEXT
    - evb_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Evaluation_seller

Purpose: Contains seller evaluations of the order (so buyers cannot behave badly).

Fields:
    - evs_ID            (PK)
    - pur_ID            (FK → purchase)
    - evs_level         INT                    -- 1-10, Overall rating
    - evs_comment       TEXT
    - evs_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Conversation

Purpose: Defines conversations (chats) in the system.

Fields:
    - con_ID            (PK)
    - con_type          CHAR(1)                -- Type: P=private, S=support, U=purchase
    - con_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Note: Can consist of 1, 2 or more users, defined by the linked messages.

Message

Purpose: Contains user messages within conversations.

Fields:
    - mes_ID            (PK)
    - u_ID              (FK → user)
    - con_ID            (FK → conversation)
    - mes_text          TEXT
    - mes_image         VARCHAR(255)           -- Image attachment
    - mes_timestamp     DATETIME DEFAULT CURRENT_TIMESTAMP

Ticket (Support)

Fields:
    - tic_ID            (PK)
    - u_ID_creator      (FK → user)            -- who created the ticket
    - con_ID            (FK → conversation)    -- Ticket chat
    - tar_ID            (FK → target)          -- Reference: product, category, order
    - tar_tb_ID         INT                    -- ID of an entry
    - tic_level         CHAR(1)                -- A=auto, 1=support1, 2=support2, C=chief
    - tic_priority      CHAR(1)                -- L=low, N=normal, H=high, U=urgent
    - tic_status        CHAR(1)                -- O=open, P=pending, S=solved, C=closed
    - tic_created       DATETIME DEFAULT CURRENT_TIMESTAMP
    - tic_closed        DATETIME NULL

Support (Assignment)

Purpose: Contains support entries for tickets (assignment of support staff).

Fields:
    - sup_ID            (PK)
    - u_ID_support      (FK → user)            -- Support staff
    - tic_ID            (FK → ticket)
    - sup_start         DATETIME               -- Start of processing
    - sup_last_act      DATETIME               -- Last activity
    - sup_status        CHAR(1)                -- A=active, I=inactive, C=closed