9d. ERD — Global Schema (cw_global)

Geography & Weather

erDiagram
    countries {
        int id PK
        string name UK
        string iso_code_2 UK
        string iso_code_3 UK
    }

    regions {
        int id PK
        int country_id FK
        string name
    }

    municipalities {
        int id PK
        int region_id FK
        string name
    }

    cities {
        int id PK
        int municipality_id FK
        string name
        string postal_code
        float latitude
        float longitude
    }

    weather_observations {
        int id PK
        int city_id FK
        date observation_date
        float temperature_max
        float temperature_min
        float temperature_mean
        float apparent_temperature_max
        float apparent_temperature_min
        float apparent_temperature_mean
        float precipitation_sum
        float rain_sum
        float snowfall_sum
        float precipitation_hours
        float windspeed_max
        float windgusts_max
        float wind_direction_dominant
        float shortwave_radiation_sum
        int weather_code
    }

    countries ||--o{ regions : ""
    regions ||--o{ municipalities : ""
    municipalities ||--o{ cities : ""
    cities ||--o{ weather_observations : ""

Geography: The hierarchy countries → regions → municipalities → cities supports any country's administrative structure (US states, German Bundesländer, French régions, etc. all map to regions).

Weather source: Data from Open-Meteo API (archive + forecast), fetched by city lat/lon coordinates. Weather codes follow the WMO standard. Weather data is shared across all clients.

Users & Roles

erDiagram
    users {
        int id PK
        string email UK
        string display_name
        boolean is_active
    }

    roles {
        int id PK
        string name UK "admin | analyst | viewer | etc."
    }

    x_user_roles {
        int id PK
        int user_id FK
        int role_id FK
    }

    roles ||--o{ x_user_roles : ""
    users ||--o{ x_user_roles : ""

Translations

erDiagram
    languages {
        int id PK
        string code UK "en | bg | de | fr"
        string name
    }

    product_translations {
        int id PK
        int product_id FK
        string field_name "e.g. product_name"
        int language_id FK
        string value
    }

    product_category_translations {
        int id PK
        int product_category_id FK
        string field_name
        int language_id FK
        string value
    }

    location_translations {
        int id PK
        int location_id FK
        string field_name
        int language_id FK
        string value
    }

    supplier_translations {
        int id PK
        int supplier_id FK
        string field_name
        int language_id FK
        string value
    }

    languages ||--o{ product_translations : ""
    languages ||--o{ product_category_translations : ""
    languages ||--o{ location_translations : ""
    languages ||--o{ supplier_translations : ""
    products ||--o{ product_translations : ""
    product_categories ||--o{ product_category_translations : ""
    locations ||--o{ location_translations : ""
    suppliers ||--o{ supplier_translations : ""

Translations: The primary-language value is stored directly on the entity column (e.g. products.product_name). Additional translations live in per-entity *_translations tables with full FK enforcement — each translation row has a typed FK to the source entity, unlike the previous generic translations table that used untyped (table_name, record_id) references.

Currency & Config

erDiagram
    currencies {
        int id PK
        string code UK "EUR | BGN | USD | CHF"
        string name
        string symbol
    }

    currency_rates {
        int id PK
        int from_currency_id FK
        int to_currency_id FK
        float rate
        date effective_date
    }

    client_config {
        int id PK
        string client_id UK
        string client_name
        int base_currency_id FK
        jsonb config
    }

    currencies ||--o{ currency_rates : "from"
    currencies ||--o{ currency_rates : "to"
    currencies ||--o{ client_config : "base"