wbids Data Model

Overview

The diagram below shows the data model of the wbids package. In our design, we primarily have R users in mind, particularly those who rely heavily on the popular data manipulation packages dplyr and data.table. For these users, having consistent and descriptive primary key column names across different tables (e.g., geography_id, series_id) simplifies writing joins across tables, reduces the risk of column name conflicts, and avoids ambiguity. We hence deliberately deviate from a common practice in data modeling to omit the entity prefix from a entity table (e.g. geography_ in the geographies table).

erDiagram
    GEOGRAPHIES ||--o{ DEBT_STATISTICS : has
    GEOGRAPHIES {
        string geography_id PK
        string geography_name
        string geography_iso2code
        string geography_type
        string capital_city
        string region_id
        string region_iso2code
        string region_name
        string admin_region_id
        string admin_region_iso2code
        string admin_region_name
        string lending_type_id
        string lending_type_iso2code
        string lending_type_name
    }
    SERIES ||--o{ DEBT_STATISTICS : has
    SERIES ||--o{ SERIES_TOPICS : has
    SERIES {
        string series_id PK
        string series_name
        int source_id
        string source_note
        string source_organization
    }
    COUNTERPARTS ||--o{ DEBT_STATISTICS : has
    COUNTERPARTS {
        string counterpart_id PK
        string counterpart_name
        string counterpart_iso2code
        string counterpart_iso3code
        string counterpart_type
    }
    SERIES_TOPICS {
        string series_id FK
        int topic_id 
        string topic_name 
    }
    DEBT_STATISTICS {
        string series_id FK
        string geography_id FK
        string counterpart_id FK
        int year
        float value
    }

Table Details

Geographies

Counterparts

Series

Debt Statistics

Assignment of Geography and Counterpart Types

The original World Bank IDS data includes a ‘country’ field, containing both countries and regions, and a ‘counterpart-area’ field, which may include countries, regions, and institutions. In our data model, these fields are renamed to ‘geography’ and ‘counterpart’ to clarify the types of entities in each column.

We also introduce corresponding type columns that specify whether a geography is a country (e.g., “Aruba”) or a region (e.g., “Africa Eastern and Southern”), and whether a counterpart is a country, region, or a special category (e.g., “Global IFIs”, “Global MDBs”). Each counterpart is represented in the geography table if it is a country or region, ensuring consistency across both tables.

Harmonization of Geography and Counterpart Names

In some cases, the IDS data provides different names for geographies that appear both in the ‘counterpart-area’ and the ‘country’ data. We use the geography names whenever they are available and drop counterpart names with different wording. For instance, if the original data features “Cote D`Ivoire, Republic Of” in the counterpart table, but the country name is “Cote d’Ivoire”, then we overwrite the former with the latter.