Shop Project - Documentation

language

Shop LDM

Part 07: Shipping & Cost Calculation

This module manages shipping, fees, zones, and international processing.

It forms the foundation for automatically calculating shipping costs, customs duties, and fees, dynamically applied based on countries, tariffs, and currencies.

The base currency is defined in the code under config.

🗺️ Stage 2 | 📚 Current | 📟 2026 06 15 | 📍 Database - Shipping & Cost Calculation

  • Country: Master data of all countries including ISO codes.
  • Shipping_zone & tb_s_zone_country: Grouping of countries into shipping zones.
  • Customs_zone & tb_c_zone_country: Definition of customs areas.
  • Currency_rate: Exchange rates to reference currency.
  • Customs_rule: Customs rules with thresholds and tax rates.
  • Carrier: Shipping carriers with country of origin.
  • Shipping_tariff: Shipping methods with limits and prices.
  • Shipping_extra: Additional options (express, insurance).
  • Fees: Shop-internal fees.

This module ensures transparent, dynamic, and international shipping cost calculation and can be used worldwide.

tb_country

Fields:
    - cun_ID               (PK)
    - cun_name             VARCHAR(100)
    - cun_international_name VARCHAR(150)
    - cun_abbrev           VARCHAR(10)            -- Abbreviation
    - cun_iso2             CHAR(2)                -- ISO 3166-1 Alpha-2 (e.g., 'CH')
    - cun_iso3             CHAR(3)                -- ISO 3166-1 Alpha-3 (e.g., 'CHE')

Note: Create a foreign key in address from 02 user and in Set from part 04 Product.

Shipping_zone

Fields:
    - siz_ID               (PK)
    - siz_name             VARCHAR(100)            -- Zone name (e.g., 'EU', 'Worldwide')
    - siz_type             CHAR(1)                 -- Type: R=Region, C=CountryGroup

Customs_zone

Fields:
    - cuz_ID               (PK)
    - cuz_name             VARCHAR(100)            -- Zone name
    - cuz_type             CHAR(1)                 -- Type: N=National, I=International

S_zone_country (Link)

Fields:
    - cun_ID               (PK → country)
    - siz_ID               (PK → shipping_zone)

C_zone_country (Link)

Fields:
    - cun_ID               (PK → country)
    - cuz_ID               (PK → customs_zone)

Carrier

Purpose: Contains shipping carriers and their countries of operation.

Fields:
    - car_ID               (PK)
    - cun_ID               (FK → country)       -- Countries of operation
    - car_name             VARCHAR(100)            -- Name (e.g., 'DHL Switzerland', 'FedEx')

Currency_rate (Exchange Rates)

Purpose: Contains exchange rates to reference currency.

Fields:
    - cur_ID               (PK)
    - cur_code             CHAR(3)                 -- ISO code (CHF, EUR, USD)
    - cur_rate             DECIMAL(12,6)           -- Relative rate (e.g., 1 EUR = 0.95 CHF)
    - cur_valid_from       DATETIME                -- Valid from
    - cur_valid_to         DATETIME NULL           -- Valid to

Note: API update possible, shop reference currency as base.

Customs_rule

Purpose: Defines customs rules for customs zones (as information for customers or pre-arranged customs fees).

Fields:
    - cus_ID               (PK)
    - cuz_ID               (FK → customs_zone)
    - cus_threshold        DECIMAL(12,2)           -- Exemption limit for duties
    - cus_tax_rate         DECIMAL(5,2)            -- VAT/sales tax %
    - cus_duty_rate        DECIMAL(5,2)            -- Customs duty %
    - cus_valid_from       DATETIME                -- Valid from
    - cus_valid_to         DATETIME NULL           -- Valid to

Shipping_tariff

Purpose: Contains shipping tariffs with weight/size limits and prices.

Fields:
    - sip_ID             (PK)
    - siz_ID             (FK → shipping_zone)      -- Destination area
    - car_ID             (FK → carrier)
    - sip_name           VARCHAR(100)              -- Name (e.g., 'Domestic Letter 100g')
    - sip_valid_from     DATETIME
    - sip_valid_to       DATETIME NULL
    - sip_max_weight     DECIMAL(12)               -- Maximum weight in g
    - sip_max_length     DECIMAL(12)               -- Maximum length in mm
    - sip_max_width      DECIMAL(12)               -- Maximum width in mm
    - sip_max_height     DECIMAL(12)               -- Maximum height in mm
    - sip_price          DECIMAL(12,2)             -- Price in reference currency
    - sip_currency       CHAR(3)                   -- ISO currency code

Note: The smallest suitable tariff is automatically selected during shipping.
Note: Create a foreign key in purchase from 06 purchase.

Shipping_extra

Purpose: Contains additional options for shipping tariffs (carrier and tariff bound).

Fields:
    - six_ID             (PK)
    - car_ID             (FK → carrier)
    - sip_ID             (FK → shipping_tariff)
    - six_name           VARCHAR(100)              -- Name (e.g., 'Express', 'Insurance')
    - six_price          DECIMAL(12,2)             -- Additional price
    - six_currency       CHAR(3)                   -- ISO currency code
    - six_valid_from     DATETIME                  -- Valid from
    - six_valid_to       DATETIME NULL             -- Valid to

Fees (Shop Fees)

Purpose: Contains shop-internal fees, independent of shipping costs (calculated only on goods).

Fields:
    - fes_ID             (PK)
    - fes_name           VARCHAR(100)              -- Fee name
    - fes_threshold      DECIMAL(12,2)             -- Minimum value from which the fee applies
    - fes_percentage     DECIMAL(5,2)              -- Percentage (e.g., 2.00 = 2%)
    - fes_valid_from     DATETIME                  -- Valid from
    - fes_valid_to       DATETIME NULL             -- Valid to

Note: Foreign key must be set in purchase from 06 purchase.