Database Schema

Complete database structure, table definitions, relationships, and field specifications for the Countries States Cities database

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.

id · integer · required

Unique identifier for the region (Primary Key)

name · varchar(100) · required

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

wikiDataId · varchar(255)

Wikidata identifier for additional reference data

created_at · timestamp

Record creation timestamp

updated_at · 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.

id · integer · required

Unique identifier for the subregion (Primary Key)

name · varchar(100) · required

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

region_id · integer · required

Foreign key reference to the parent region

wikiDataId · varchar(255)

Wikidata identifier for additional reference data

created_at · timestamp

Record creation timestamp

updated_at · 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.

id · integer · required

Unique identifier for the country (Primary Key)

name · varchar(100) · required

Official English name of the country

iso3 · char(3) · required

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

iso2 · char(2) · required

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

numeric_code · char(3)

ISO 3166-1 numeric code

phone_code · varchar(255)

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

capital · varchar(255)

Name of the country’s capital city

currency · char(3)

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

currency_name · varchar(100)

Full name of the currency

currency_symbol · varchar(10)

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

tld · varchar(10)

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

native · varchar(255)

Native name of the country in local language/script

region · varchar(100)

Region name (denormalized for quick access)

subregion · varchar(100)

Subregion name (denormalized for quick access)

region_id · integer

Foreign key reference to the regions table

subregion_id · integer

Foreign key reference to the subregions table

nationality · varchar(100)

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

latitude · decimal(10,8)

Geographical latitude coordinate of country center

longitude · decimal(11,8)

Geographical longitude coordinate of country center

emoji · varchar(10)

Unicode emoji flag representation

emojiU · varchar(20)

Unicode code points for the flag emoji

population · integer

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

gdp · decimal

Gross domestic product in USD billions (approximate)

area_sq_km · decimal

Total area in square kilometres

postal_code_format · varchar(255)

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

postal_code_regex · varchar(255)

Regular expression for validating postal codes for this country

timezones · json

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

translations · json

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

wikiDataId · 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.).

id · integer · required

Unique identifier for the state (Primary Key)

name · varchar(255) · required

Official name of the state/province/region

country_id · integer · required

Foreign key reference to the parent country

country_code · char(2) · required

ISO 2-letter country code for quick reference

country_name · varchar(100)

Country name (denormalized for performance)

state_code · varchar(10)

Official state/province code when available

type · varchar(191)

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

fips_code · varchar(10)

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

iso3166_2 · varchar(10)

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

level · 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.

parent_id · 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.

native · varchar(255)

State name in the local/native language and script

timezone · varchar(255)

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

translations · json

JSON object of state name translations keyed by locale code

population · integer

State population estimate

wikiDataId · varchar(255)

Wikidata identifier for cross-referencing additional metadata

latitude · decimal(10,8)

Geographical latitude coordinate of state center

longitude · 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.

id · integer · required

Unique identifier for the city (Primary Key)

name · varchar(255) · required

Official name of the city/town

state_id · integer · required

Foreign key reference to the parent state

state_code · varchar(10)

State code for quick reference

state_name · varchar(255)

State name (denormalized for performance)

country_id · integer · required

Foreign key reference to the country

country_code · char(2) · required

ISO 2-letter country code for quick reference

country_name · varchar(100)

Country name (denormalized for performance)

type · 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.

level · integer

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

parent_id · integer

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

native · varchar(255)

City name in the local/native language and script

population · integer

City population estimate

timezone · varchar(255)

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

translations · json

JSON object of city name translations keyed by locale code

latitude · decimal(10,8) · required

Geographical latitude coordinate

longitude · decimal(11,8) · required

Geographical longitude coordinate

wikiDataId · 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.

id · integer · required

Unique identifier for the postcode record (Primary Key)

postcode · varchar(20) · required

The postal / ZIP code string (e.g., 90210, SW1A 1AA, 110001)

country_code · char(2) · required

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

type · enum

Granularity of the postcode. One of:

• full — complete postcode identifying a specific delivery point or small area
• outward — first part of a UK-style postcode identifying the postal district
• sector — intermediate granularity within a district
• district — broadest level, covering an entire postal district

source · varchar(255)

Data source attribution for the postcode record

locality_name · varchar(255)

Name of the locality or area associated with this postcode

wikiDataId · varchar(255)

Wikidata identifier for cross-referencing

Sample Data:

[
  {
    "id": 1,
    "postcode": "90210",
    "country_code": "US",
    "type": "full",
    "source": "GeoNames",
    "locality_name": "Beverly Hills",
    "wikiDataId": null
  },
  {
    "id": 2,
    "postcode": "SW1A 1AA",
    "country_code": "GB",
    "type": "full",
    "source": "GeoNames",
    "locality_name": "London",
    "wikiDataId": null
  }
]

The postcodes table is available in the self-hosted database exports. See the postcodes reference for coverage details and query examples.

Indexes and Performance

Primary Indexes

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

Foreign Key Indexes

MySQL

-- 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

-- 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
);

PostgreSQL

-- 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()
);

SQLite

-- 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:

Get Country with States

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.