​
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
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",
  "latitude": 37.09024000,
  "longitude": -95.71289100,
  "emoji": "🇺🇸",
  "emojiU": "U+1F1FA U+1F1F8"
}

​
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, etc.)
​
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",
  "type": "state",
  "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)
​
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",
  "latitude": 34.05223000,
  "longitude": -118.24368000,
  "wikiDataId": "Q65"
}

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

​
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

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