Database Schema - Country State City Documentation

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)

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.

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.

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

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.

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.

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

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

Next Steps

Installation Guide

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

​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