> ## Documentation Index > Fetch the complete documentation index at: https://docs.countrystatecity.in/llms.txt > Use this file to discover all available pages before exploring further. # 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.

```mermaid theme={null} erDiagram regions { int id PK string name string wikiDataId timestamp created_at timestamp updated_at } subregions { int id PK string name int region_id FK string wikiDataId timestamp created_at timestamp updated_at } countries { int id PK string name string iso3 string iso2 string numeric_code string phone_code string capital string currency string currency_name string currency_symbol string tld string native string region string subregion int region_id FK int subregion_id FK string nationality int population decimal gdp decimal area_sq_km string postal_code_format string postal_code_regex json timezones json translations decimal latitude decimal longitude string emoji string emojiU string wikiDataId timestamp created_at timestamp updated_at } states { int id PK string name int country_id FK string country_code string country_name string state_code string fips_code string iso3166_2 string type int level int parent_id FK string native string timezone json translations int population decimal latitude decimal longitude string wikiDataId timestamp created_at timestamp updated_at } cities { int id PK string name int state_id FK string state_code string state_name int country_id FK string country_code string country_name string type int level int parent_id FK string native int population string timezone json translations decimal latitude decimal longitude string wikiDataId timestamp created_at timestamp updated_at } postcodes { int id PK string postcode string country_code FK string type string source string locality_name string wikiDataId timestamp created_at timestamp updated_at } regions ||--o{ subregions : contains subregions ||--o{ countries : contains regions ||--o{ countries : contains countries ||--o{ states : contains states ||--o{ cities : contains countries ||--o{ postcodes : contains ```

## Core Tables ### 1. Regions Table The top-level geographical divisions based on UN regional classifications. Unique identifier for the region (Primary Key) Official name of the region (e.g., "Europe", "Asia", "Africa") Wikidata identifier for additional reference data Record creation timestamp Last modification timestamp **Sample Data:** ```json theme={null} { "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. Unique identifier for the subregion (Primary Key) Official name of the subregion (e.g., "Western Europe", "Southern Asia") Foreign key reference to the parent region Wikidata identifier for additional reference data Record creation timestamp Last modification timestamp **Sample Data:** ```json theme={null} { "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. Unique identifier for the country (Primary Key) Official English name of the country ISO 3166-1 alpha-3 code (e.g., "USA", "IND", "GBR") ISO 3166-1 alpha-2 code (e.g., "US", "IN", "GB") ISO 3166-1 numeric code International dialing code (e.g., "+1", "+91", "+44") Name of the country's capital city ISO 4217 currency code (e.g., "USD", "EUR", "INR") Full name of the currency Currency symbol (e.g., "\$", "€", "₹") Top-level domain (e.g., ".us", ".in", ".uk") Native name of the country in local language/script Region name (denormalized for quick access) Subregion name (denormalized for quick access) Foreign key reference to the regions table Foreign key reference to the subregions table Demonym for country citizens (e.g., "American", "Indian", "British") Geographical latitude coordinate of country center Geographical longitude coordinate of country center Unicode emoji flag representation Unicode code points for the flag emoji Country population (may be null for territories with no dedicated census) Gross domestic product in USD billions (approximate) Total area in square kilometres Human-readable postal code format pattern (e.g., `#####` for US zip codes) Regular expression for validating postal codes for this country JSON array of timezone objects for the country. Each object contains `zoneName`, `gmtOffset`, `gmtOffsetName`, `abbreviation`, and `tzName`. JSON object of country name translations keyed by locale code (e.g., `"fr": "États-Unis"`, `"de": "Vereinigte Staaten"`). 19 languages supported. Wikidata identifier for cross-referencing additional metadata **Sample Data:** ```json theme={null} { "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.). Unique identifier for the state (Primary Key) Official name of the state/province/region Foreign key reference to the parent country ISO 2-letter country code for quick reference Country name (denormalized for performance) Official state/province code when available Type of administrative division (state, province, region, municipality, etc.) FIPS (Federal Information Processing Standards) code, primarily used for US states ISO 3166-2 subdivision code (e.g., `US-CA`, `IN-MH`, `GB-ENG`) 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. 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. State name in the local/native language and script Primary IANA timezone identifier for the state (e.g., `America/Los_Angeles`) JSON object of state name translations keyed by locale code State population estimate Wikidata identifier for cross-referencing additional metadata Geographical latitude coordinate of state center Geographical longitude coordinate of state center **Sample Data:** ```json theme={null} { "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. Unique identifier for the city (Primary Key) Official name of the city/town Foreign key reference to the parent state State code for quick reference State name (denormalized for performance) Foreign key reference to the country ISO 2-letter country code for quick reference Country name (denormalized for performance) Place classification inherited from GeoNames (e.g., `city`, `town`, `adm2`, `county`). See the [city types reference](/database/city-types) for all 35 values and filtering guidance. Administrative hierarchy level within the state, mirroring the state-level structure where applicable Foreign key to the parent city row for nested subdivisions (e.g., a district within a city) City name in the local/native language and script City population estimate IANA timezone identifier for the city (e.g., `America/Los_Angeles`) JSON object of city name translations keyed by locale code Geographical latitude coordinate Geographical longitude coordinate Wikidata identifier for additional reference data **Sample Data:** ```json theme={null} { "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. Unique identifier for the postcode record (Primary Key) The postal / ZIP code string (e.g., `90210`, `SW1A 1AA`, `110001`) ISO 3166-1 alpha-2 country code linking to the countries table 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 Data source attribution for the postcode record Name of the locality or area associated with this postcode Wikidata identifier for cross-referencing **Sample Data:** ```json theme={null} [ { "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](/database/postcodes) 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 ```sql MySQL theme={null} -- 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); ``` ```sql PostgreSQL theme={null} -- 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: ```sql theme={null} -- 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 ); ``` ```sql theme={null} -- 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() ); ``` ```sql theme={null} -- 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. ```sql theme={null} -- 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: ```sql Get Country with States theme={null} 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; ``` ```sql Get Cities by State theme={null} 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; ``` ```sql Regional Statistics theme={null} 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 Learn how to set up the database in your preferred environment.