Database Schema

Database Architecture

The Countries States Cities database follows a hierarchical structure with clear relationships between geographical entities. The schema is designed to be normalized, efficient, and consistent across all supported database formats.

Core Tables

1. Regions Table

The top-level geographical divisions based on UN regional classifications.

integer

required

Unique identifier for the region (Primary Key)

varchar(100)

required

Official name of the region (e.g., “Europe”, “Asia”, “Africa”)

varchar(255)

Wikidata identifier for additional reference data

timestamp

Record creation timestamp

timestamp

Last modification timestamp

Sample Data:

{
  "id": 3,
  "name": "Europe",
  "wikiDataId": "Q46",
  "created_at": "2023-01-15T10:00:00Z",
  "updated_at": "2023-01-15T10:00:00Z"
}

2. Subregions Table

Subdivisions of regions providing more granular geographical classification.

integer

required

Unique identifier for the subregion (Primary Key)

varchar(100)

required

Official name of the subregion (e.g., “Western Europe”, “Southern Asia”)

integer

required

Foreign key reference to the parent region

varchar(255)

Wikidata identifier for additional reference data

timestamp

Record creation timestamp

timestamp

Last modification timestamp

Sample Data:

{
  "id": 155,
  "name": "Western Europe",
  "region_id": 3,
  "wikiDataId": "Q27496",
  "created_at": "2023-01-15T10:00:00Z",
  "updated_at": "2023-01-15T10:00:00Z"
}

3. Countries Table

Complete country information with ISO codes, economic data, and geographical coordinates.

integer

required

Unique identifier for the country (Primary Key)

varchar(100)

required

Official English name of the country

char(3)

required

ISO 3166-1 alpha-3 code (e.g., “USA”, “IND”, “GBR”)

char(2)

required

ISO 3166-1 alpha-2 code (e.g., “US”, “IN”, “GB”)

char(3)

ISO 3166-1 numeric code

varchar(255)

International dialing code (e.g., “+1”, “+91”, “+44”)

varchar(255)

Name of the country’s capital city

char(3)

ISO 4217 currency code (e.g., “USD”, “EUR”, “INR”)

varchar(100)

Full name of the currency

varchar(10)

Currency symbol (e.g., ”$”, ”€”, ”₹”)

varchar(10)

Top-level domain (e.g., “.us”, “.in”, “.uk”)

varchar(255)

Native name of the country in local language/script

varchar(100)

Region name (denormalized for quick access)

varchar(100)

Subregion name (denormalized for quick access)

integer

Foreign key reference to the regions table

integer

Foreign key reference to the subregions table

varchar(100)

Demonym for country citizens (e.g., “American”, “Indian”, “British”)

decimal(10,8)

Geographical latitude coordinate of country center

decimal(11,8)

Geographical longitude coordinate of country center

varchar(10)

Unicode emoji flag representation

varchar(20)

Unicode code points for the flag emoji

integer

Country population (may be null for territories with no dedicated census)

decimal

Gross domestic product in USD billions (approximate)

decimal

Total area in square kilometres

varchar(255)

Human-readable postal code format pattern (e.g., ##### for US zip codes)

varchar(255)

Regular expression for validating postal codes for this country

json

JSON array of timezone objects for the country. Each object contains zoneName, gmtOffset, gmtOffsetName, abbreviation, and tzName.

json

JSON object of country name translations keyed by locale code (e.g., "fr": "États-Unis", "de": "Vereinigte Staaten"). 19 languages supported.

varchar(255)

Wikidata identifier for cross-referencing additional metadata

Sample Data:

{
  "id": 231,
  "name": "United States",
  "iso3": "USA",
  "iso2": "US",
  "numeric_code": "840",
  "phone_code": "+1",
  "capital": "Washington",
  "currency": "USD",
  "currency_name": "US Dollar",
  "currency_symbol": "$",
  "tld": ".us",
  "native": "United States",
  "region": "Americas",
  "subregion": "Northern America",
  "region_id": 2,
  "subregion_id": 21,
  "nationality": "American",
  "population": 331000000,
  "gdp": 23000.00,
  "area_sq_km": 9833517.00,
  "postal_code_format": "#####-####",
  "postal_code_regex": "^\\d{5}(-\\d{4})?$",
  "timezones": [
    { "zoneName": "America/New_York", "gmtOffset": -18000, "gmtOffsetName": "UTC-05:00", "abbreviation": "EST", "tzName": "Eastern Standard Time" }
  ],
  "translations": {
    "fr": "États-Unis", "de": "Vereinigte Staaten", "hi": "संयुक्त राज्य", "zh-CN": "美国"
  },
  "latitude": 37.09024000,
  "longitude": -95.71289100,
  "emoji": "🇺🇸",
  "emojiU": "U+1F1FA U+1F1F8",
  "wikiDataId": "Q30"
}

4. States Table

Administrative divisions within countries (states, provinces, regions, etc.).

integer

required

Unique identifier for the state (Primary Key)

varchar(255)

required

Official name of the state/province/region

integer

required

Foreign key reference to the parent country

char(2)

required

ISO 2-letter country code for quick reference

varchar(100)

Country name (denormalized for performance)

varchar(10)

Official state/province code when available

varchar(191)

Type of administrative division (state, province, region, municipality, etc.)

varchar(10)

FIPS (Federal Information Processing Standards) code, primarily used for US states

varchar(10)

ISO 3166-2 subdivision code (e.g., US-CA, IN-MH, GB-ENG)

integer

Hierarchy level within the country. 1 = top-level region/state, 2 = department/province. Used in countries with multi-tier administration (France, Italy). Null for countries with a flat structure.

integer

Foreign key to the parent state row for level-2 subdivisions (e.g., a French department points to its region). Null for level-1 states.

varchar(255)

State name in the local/native language and script

varchar(255)

Primary IANA timezone identifier for the state (e.g., America/Los_Angeles)

json

JSON object of state name translations keyed by locale code

integer

State population estimate

varchar(255)

Wikidata identifier for cross-referencing additional metadata

decimal(10,8)

Geographical latitude coordinate of state center

decimal(11,8)

Geographical longitude coordinate of state center

Sample Data:

{
  "id": 1416,
  "name": "California",
  "country_id": 231,
  "country_code": "US",
  "country_name": "United States",
  "state_code": "CA",
  "fips_code": "06",
  "iso3166_2": "US-CA",
  "type": "state",
  "level": 1,
  "parent_id": null,
  "native": "California",
  "timezone": "America/Los_Angeles",
  "translations": { "fr": "Californie", "de": "Kalifornien", "hi": "कैलिफ़ोर्निया" },
  "population": 39538223,
  "wikiDataId": "Q99",
  "latitude": 36.77826100,
  "longitude": -119.41793240
}

5. Cities Table

Cities, towns, and other populated places within states/countries.

integer

required

Unique identifier for the city (Primary Key)

varchar(255)

required

Official name of the city/town

integer

required

Foreign key reference to the parent state

varchar(10)

State code for quick reference

varchar(255)

State name (denormalized for performance)

integer

required

Foreign key reference to the country

char(2)

required

ISO 2-letter country code for quick reference

varchar(100)

Country name (denormalized for performance)

varchar(191)

Place classification inherited from GeoNames (e.g., city, town, adm2, county). See the city types reference for all 35 values and filtering guidance.

integer

Administrative hierarchy level within the state, mirroring the state-level structure where applicable

integer

Foreign key to the parent city row for nested subdivisions (e.g., a district within a city)

varchar(255)

City name in the local/native language and script

integer

City population estimate

varchar(255)

IANA timezone identifier for the city (e.g., America/Los_Angeles)

json

JSON object of city name translations keyed by locale code

decimal(10,8)

required

Geographical latitude coordinate

decimal(11,8)

required

Geographical longitude coordinate

varchar(255)

Wikidata identifier for additional reference data

Sample Data:

{
  "id": 111968,
  "name": "Los Angeles",
  "state_id": 1416,
  "state_code": "CA",
  "state_name": "California",
  "country_id": 231,
  "country_code": "US",
  "country_name": "United States",
  "type": "adm2",
  "level": null,
  "parent_id": null,
  "native": "Los Angeles",
  "population": 3898747,
  "timezone": "America/Los_Angeles",
  "translations": { "fr": "Los Angeles", "de": "Los Angeles", "hi": "लॉस एंजेलेस" },
  "latitude": 34.05223000,
  "longitude": -118.24368000,
  "wikiDataId": "Q65"
}

6. Postcodes Table

844,248 postal codes across 125 countries with type classification and source tracking. The authoritative column and API property is code — not postcode.

integer

required

Unique identifier for the postcode record (Primary Key)

varchar(20)

required

The postal / ZIP code string, as stored (e.g., 110001, GY1 1AA, 11104)

char(2)

required

ISO 3166-1 alpha-2 country code linking to the countries table

varchar(255)

State/province code, when the postcode is linked to one

varchar(255)

Name of the locality or area associated with this postcode

varchar(32)

A source-provided classification, not a fixed database enum — new values can appear as more sources are imported. The current dataset uses full (complete postcode, most countries), area (broader geographic area, e.g. Netherlands, Iran, UK), fsa (Canadian Forward Sortation Area), po_box, and street (South Africa’s distinct PO Box vs. street-delivery codes). See Postcode Types for the full breakdown.

integer

required

Foreign key reference to the parent country

integer

Foreign key reference to the linked state, when applicable

integer

Foreign key reference to the linked city, when applicable

decimal(10,8)

Geographical latitude coordinate of the postcode area

decimal(11,8)

Geographical longitude coordinate of the postcode area

varchar(64)

Data source attribution for the postcode record (e.g. openplz, wikidata, census)

varchar(255)

Wikidata identifier for cross-referencing

Sample Data:

[
  {
    "id": 131878,
    "code": "110001",
    "country_code": "IN",
    "state_code": "DL",
    "locality_name": "Sansad Marg",
    "type": "full",
    "country_id": 101,
    "state_id": 4021,
    "city_id": null,
    "latitude": null,
    "longitude": null,
    "source": "india-post",
    "wikiDataId": null
  },
  {
    "id": 670115,
    "code": "11104",
    "country_code": "LK",
    "state_code": "12",
    "locality_name": "Mabola",
    "type": "full",
    "country_id": 208,
    "state_id": 2804,
    "city_id": null,
    "latitude": 7.00628330,
    "longitude": 79.89248280,
    "source": "sri-lanka-post-via-madurapa",
    "wikiDataId": null
  }
]

The postcodes table is available in the self-hosted database exports and via the hosted API — see Get Postcode by Code and Get Postcodes by Country. See the postcodes reference for coverage details and self-hosted query examples.

API access levels. The hosted API tiers postcode fields the same way it tiers cities and countries: id, code, country_code, state_code, locality_name, and type are on every plan (Basic). country_id, state_id, city_id, latitude, longitude, source, and wikiDataId require Supporter+. created_at, updated_at, and flag are not exposed publicly. Self-hosted database exports are not access-tiered — all columns are present.

Indexes and Performance

Primary Indexes

All tables include clustered primary key indexes on the id field for optimal performance.

Foreign Key Indexes

-- States table indexes
CREATE INDEX idx_states_country_id ON states(country_id);
CREATE INDEX idx_states_country_code ON states(country_code);

-- Cities table indexes  
CREATE INDEX idx_cities_state_id ON cities(state_id);
CREATE INDEX idx_cities_country_id ON cities(country_id);
CREATE INDEX idx_cities_country_code ON cities(country_code);

-- Countries table indexes
CREATE INDEX idx_countries_region_id ON countries(region_id);
CREATE INDEX idx_countries_subregion_id ON countries(subregion_id);
CREATE INDEX idx_countries_iso2 ON countries(iso2);
CREATE INDEX idx_countries_iso3 ON countries(iso3);

-- Subregions table indexes
CREATE INDEX idx_subregions_region_id ON subregions(region_id);

-- States table indexes
CREATE INDEX idx_states_country_id ON states(country_id);
CREATE INDEX idx_states_country_code ON states(country_code);

-- Cities table indexes
CREATE INDEX idx_cities_state_id ON cities(state_id);
CREATE INDEX idx_cities_country_id ON cities(country_id);
CREATE INDEX idx_cities_country_code ON cities(country_code);

-- Countries table indexes
CREATE INDEX idx_countries_region_id ON countries(region_id);
CREATE INDEX idx_countries_subregion_id ON countries(subregion_id);
CREATE INDEX idx_countries_iso2 ON countries(iso2);
CREATE INDEX idx_countries_iso3 ON countries(iso3);

-- Subregions table indexes
CREATE INDEX idx_subregions_region_id ON subregions(region_id);

Search Indexes

For full-text search capabilities, consider adding text search indexes on name fields based on your database platform’s capabilities.

Data Types by Platform

Different database platforms use slightly different data type specifications:

MySQL
PostgreSQL
SQLite

-- Example table definition for MySQL
CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    iso2 CHAR(2) NOT NULL,
    iso3 CHAR(3) NOT NULL,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Example table definition for PostgreSQL
CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    iso2 CHAR(2) NOT NULL,
    iso3 CHAR(3) NOT NULL,
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Example table definition for SQLite
CREATE TABLE countries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    iso2 TEXT NOT NULL,
    iso3 TEXT NOT NULL,
    latitude REAL,
    longitude REAL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Relationships and Constraints

Foreign Key Constraints

Foreign key constraints are enforced in most SQL databases but may need to be handled at the application level in some NoSQL implementations.

-- Foreign key relationships
ALTER TABLE subregions 
ADD CONSTRAINT fk_subregions_region 
FOREIGN KEY (region_id) REFERENCES regions(id);

ALTER TABLE countries 
ADD CONSTRAINT fk_countries_region 
FOREIGN KEY (region_id) REFERENCES regions(id);

ALTER TABLE countries 
ADD CONSTRAINT fk_countries_subregion 
FOREIGN KEY (subregion_id) REFERENCES subregions(id);

ALTER TABLE states 
ADD CONSTRAINT fk_states_country 
FOREIGN KEY (country_id) REFERENCES countries(id);

ALTER TABLE cities 
ADD CONSTRAINT fk_cities_state 
FOREIGN KEY (state_id) REFERENCES states(id);

ALTER TABLE cities 
ADD CONSTRAINT fk_cities_country 
FOREIGN KEY (country_id) REFERENCES countries(id);

Data Integrity Rules

Required Fields

All primary keys must be non-null and unique
Country names, ISO codes are required
State and city names are required
Geographic coordinates are required for cities

Format Validation

ISO2 codes must be exactly 2 characters
ISO3 codes must be exactly 3 characters
Phone codes must start with ”+”
Latitude must be between -90 and 90
Longitude must be between -180 and 180

Referential Integrity

States must belong to valid countries
Cities must belong to valid states and countries
Countries must belong to valid regions and subregions

Common Queries

Here are some frequently used query patterns:

SELECT 
    c.name AS country_name,
    c.iso2,
    s.name AS state_name,
    s.state_code
FROM countries c
LEFT JOIN states s ON c.id = s.country_id  
WHERE c.iso2 = 'US'
ORDER BY s.name;

SELECT 
    ci.name AS city_name,
    ci.latitude,
    ci.longitude,
    s.name AS state_name,
    c.name AS country_name
FROM cities ci
JOIN states s ON ci.state_id = s.id
JOIN countries c ON s.country_id = c.id
WHERE s.country_code = 'US' AND s.state_code = 'CA'
ORDER BY ci.name;

SELECT 
    r.name AS region_name,
    COUNT(DISTINCT c.id) AS country_count,
    COUNT(DISTINCT s.id) AS state_count,
    COUNT(ci.id) AS city_count
FROM regions r
LEFT JOIN countries c ON r.id = c.region_id
LEFT JOIN states s ON c.id = s.country_id  
LEFT JOIN cities ci ON s.id = ci.state_id
GROUP BY r.id, r.name
ORDER BY country_count DESC;

Next Steps

Installation Guide

Learn how to set up the database in your preferred environment.

Database Documentation

Installation Guides

Support

Database Architecture

Core Tables

1. Regions Table

2. Subregions Table

3. Countries Table

4. States Table

5. Cities Table

6. Postcodes Table

Indexes and Performance

Primary Indexes

Foreign Key Indexes

Search Indexes

Data Types by Platform

Relationships and Constraints

Foreign Key Constraints

Data Integrity Rules

Common Queries

Next Steps

Installation Guide

​Database Architecture

​Core Tables

​1. Regions Table

​2. Subregions Table

​3. Countries Table

​4. States Table

​5. Cities Table

​6. Postcodes Table

​Indexes and Performance

​Primary Indexes

​Foreign Key Indexes

​Search Indexes

​Data Types by Platform

​Relationships and Constraints

​Foreign Key Constraints

​Data Integrity Rules

​Common Queries

​Next Steps

Installation Guide

Database Architecture

Core Tables

1. Regions Table

2. Subregions Table

3. Countries Table

4. States Table

5. Cities Table

6. Postcodes Table

Indexes and Performance

Primary Indexes

Foreign Key Indexes

Search Indexes

Data Types by Platform

Relationships and Constraints

Foreign Key Constraints

Data Integrity Rules

Common Queries

Next Steps