Geographic Database Design - Relational Schema for Countries and Cities

Objective

Create a practical guide on how to design and implement a database schema that models the geographic structure of countries, regions, cities, and addresses.

Prerequisites

To follow this article, I recommend having installed:

Design Decisions

Principles

The design follows these fundamental principles:

Hierarchical Structure

The geographic hierarchy is designed to allow efficient searches at any level:

COUNTRY (countries)
  ├── REGION (regions)
  │   ├── SUBREGION (subregions)
  │   │   ├── CITY (cities)
  │   │   │   └── ADDRESS (addresses)

Current Limitations

This article does not cover zip codes within cities. The reason is that there can be multiple zip codes per city. The goal is to share a simple and valid minimal schema.

Later, the reader can implement improvements or scale the model according to their needs.

Entity Relationship Diagram

The database schema to be created is as follows:

erDiagram
    direction LR
    COUNTRIES ||--o{ REGIONS : has
    REGIONS ||--o{ SUBREGIONS : has
    SUBREGIONS ||--o{ CITIES : has
    CITIES ||--o{ ADDRESSES : has

    COUNTRIES {
        string iso_code PK
        string name
    }

    REGIONS {
        bigint region_id PK
        string name
        string iso_code
        string country_iso FK
    }

    SUBREGIONS {
        bigint subregion_id PK
        string name
        string code
        bigint region_id FK
    }

    CITIES {
        bigint city_id PK
        string name
        bigint subregion_id FK
    }

    ADDRESSES {
        bigint address_id PK
        string street
        string number
        string postal_code
        bigint city_id FK
    }

Diagram Legend

• PK = Primary Key
• FK = Foreign Key
• ||—o{ = 1-to-Many Relationship (1 to N)

Tables and Relationships

Countries Table

Stores information about countries with a unique ISO code:

CREATE TABLE public.countries (
  name text NOT NULL,
  iso_code text NOT NULL,
  CONSTRAINT countries_pkey PRIMARY KEY (iso_code)
);

Fields:

• name: Country name (e.g., “Spain”, “Mexico”)
• iso_code: Two-character ISO 3166-1 alpha-2 code (e.g., “ES”, “MX”). Primary key.

Purpose: To store the base catalog of countries. The ISO code guarantees worldwide uniqueness.

Regions Table

Stores regions or states within a country:

CREATE TABLE public.regions (
  region_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  iso_code text,
  country_iso text,
  CONSTRAINT regions_pkey PRIMARY KEY (region_id),
  CONSTRAINT regions_country_iso_fkey FOREIGN KEY (country_iso) REFERENCES public.countries(iso_code)
);

Fields:

• region_id: Unique autoincremental identifier
• name: Region name (e.g., “Madrid”, “Mexico City”, “Bavaria”)
• iso_code: Optional ISO code of the region
• country_iso: Foreign reference to the country

Subregions Table

Stores subdivisions within a region:

CREATE TABLE public.subregions (
  subregion_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  code text,
  region_id bigint NOT NULL,
  CONSTRAINT subregions_pkey PRIMARY KEY (subregion_id),
  CONSTRAINT subregions_region_id_fkey FOREIGN KEY (region_id) REFERENCES public.regions(region_id)
);

Fields:

• subregion_id: Unique autoincremental identifier
• name: Subregion name (e.g., “Province”, “County”)
• code: Optional subregion code
• region_id: Foreign reference to the region

Cities Table

Stores cities or municipalities:

CREATE TABLE public.cities (
  city_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  subregion_id bigint NOT NULL,
  CONSTRAINT cities_pkey PRIMARY KEY (city_id),
  CONSTRAINT cities_subregion_id_fkey FOREIGN KEY (subregion_id) REFERENCES public.subregions(subregion_id)
);

Fields:

• city_id: Unique autoincremental identifier
• name: City name (e.g., “Madrid”, “Barcelona”)
• subregion_id: Foreign reference to the subregion

Addresses Table

Stores complete postal addresses:

CREATE TABLE public.addresses (
  address_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  street text NOT NULL,
  city_id bigint NOT NULL,
  postal_code text NOT NULL,
  number text NOT NULL,
  place_id uuid NOT NULL,
  CONSTRAINT addresses_pkey PRIMARY KEY (address_id),
  CONSTRAINT addresses_city_id_fkey FOREIGN KEY (city_id) REFERENCES public.cities(city_id)
);

Fields:

• address_id: Unique autoincremental identifier
• street: Street name (e.g., “Gran Vía Street”)
• city_id: Foreign reference to the city
• postal_code: Zip code (e.g., “28001”)
• number: Street number (e.g., “42”, “42A”)

Common Query Patterns

1. Get full address with geographic hierarchy

SELECT
  addr.address_id,
  CONCAT(addr.street, ' ', addr.number) AS address,
  addr.postal_code,
  city.name                             AS city,
  subreg.name                           AS province,
  reg.name                              AS region,
  country.name                          AS country
FROM addresses  AS addr
JOIN cities     AS city    ON addr.city_id = city.city_id
JOIN subregions AS subreg  ON city.subregion_id = subreg.subregion_id
JOIN regions    AS reg     ON subreg.region_id = reg.region_id
JOIN countries  AS country ON reg.country_iso = country.iso_code
WHERE addr.address_id = 1;

2. Search for all cities in a region

SELECT 
  city.city_id, 
  city.name
FROM cities     AS city
JOIN subregions AS subreg ON city.subregion_id = subreg.subregion_id
JOIN regions    AS reg    ON subreg.region_id = reg.region_id
WHERE reg.name = 'Madrid';

3. Count addresses by country

SELECT
  country.name            AS country,
  COUNT(addr.address_id)  AS total_addresses
FROM addresses  AS addr
JOIN cities     AS city    ON addr.city_id = city.city_id
JOIN subregions AS subreg  ON city.subregion_id = subreg.subregion_id
JOIN regions    AS reg     ON subreg.region_id = reg.region_id
JOIN countries  AS country ON reg.country_iso = country.iso_code
GROUP BY country.iso_code, country.name
ORDER BY total_addresses DESC;

Optimization and Best Practices

Recommended Indexes

-- Index on country ISO code
CREATE INDEX idx_regions_country_iso ON regions(country_iso);

-- Index on region_id (frequent JOINs)
CREATE INDEX idx_subregions_region_id ON subregions(region_id);

-- Index on subregion_id (frequent JOINs)
CREATE INDEX idx_cities_subregion_id ON cities(subregion_id);

-- Index on city_id (frequent JOINs)
CREATE INDEX idx_addresses_city_id ON addresses(city_id);

-- Index on postal_code (city zip code searches)
CREATE INDEX idx_addresses_postal_code ON addresses(postal_code);

Performance Considerations

• Caching: Consider caching countries, regions, and cities (they rarely change)
• Pagination: For large listings, always paginate the results
• Partitioning: For tables with >10M records, consider partitioning by country

Questions or suggestions? This schema can be adapted to your specific needs. For example, you could add fields for time zones, population, or additional administrative codes.

Scripts

Full SQL Schema

Below is the full SQL ready to copy and run in your PostgreSQL database:

-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS public;

-- Countries Table
CREATE TABLE public.countries (
  name text NOT NULL,
  iso_code text NOT NULL,
  CONSTRAINT countries_pkey PRIMARY KEY (iso_code)
);

-- Regions Table
CREATE TABLE public.regions (
  region_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  iso_code text,
  country_iso text,
  CONSTRAINT regions_pkey PRIMARY KEY (region_id),
  CONSTRAINT regions_country_iso_fkey FOREIGN KEY (country_iso) REFERENCES public.countries(iso_code)
);

-- Subregions Table
CREATE TABLE public.subregions (
  subregion_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  code text,
  region_id bigint NOT NULL,
  CONSTRAINT subregions_pkey PRIMARY KEY (subregion_id),
  CONSTRAINT subregions_region_id_fkey FOREIGN KEY (region_id) REFERENCES public.regions(region_id)
);

-- Cities Table
CREATE TABLE public.cities (
  city_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  subregion_id bigint NOT NULL,
  CONSTRAINT cities_pkey PRIMARY KEY (city_id),
  CONSTRAINT cities_subregion_id_fkey FOREIGN KEY (subregion_id) REFERENCES public.subregions(subregion_id)
);

-- Addresses Table
CREATE TABLE public.addresses (
  address_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  street text NOT NULL,
  city_id bigint NOT NULL,
  postal_code text NOT NULL,
  number text NOT NULL,
  place_id uuid NOT NULL,
  CONSTRAINT addresses_pkey PRIMARY KEY (address_id),
  CONSTRAINT addresses_city_id_fkey FOREIGN KEY (city_id) REFERENCES public.cities(city_id)
);

-- Create indexes to optimize queries
CREATE INDEX idx_regions_country_iso ON regions(country_iso);
CREATE INDEX idx_subregions_region_id ON subregions(region_id);
CREATE INDEX idx_cities_subregion_id ON cities(subregion_id);
CREATE INDEX idx_addresses_city_id ON addresses(city_id);
CREATE INDEX idx_addresses_postal_code ON addresses(postal_code);

Migrations with Flyway

Flyway is a popular tool for managing database migrations. To use this schema with Flyway, create a migration file in the src/main/resources/db/migration/ folder:

File: V1__Create_Geographic_Schema.sql

-- V1__Create_Geographic_Schema.sql
-- Description: Create base geographic schema with countries, regions, cities and addresses tables
-- Date: 2026-04-14

CREATE TABLE IF NOT EXISTS public.countries (
  name text NOT NULL,
  iso_code text NOT NULL,
  CONSTRAINT countries_pkey PRIMARY KEY (iso_code)
);

CREATE TABLE IF NOT EXISTS public.regions (
  region_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  iso_code text,
  country_iso text,
  CONSTRAINT regions_pkey PRIMARY KEY (region_id),
  CONSTRAINT regions_country_iso_fkey FOREIGN KEY (country_iso) REFERENCES public.countries(iso_code)
);

CREATE TABLE IF NOT EXISTS public.subregions (
  subregion_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  code text,
  region_id bigint NOT NULL,
  CONSTRAINT subregions_pkey PRIMARY KEY (subregion_id),
  CONSTRAINT subregions_region_id_fkey FOREIGN KEY (region_id) REFERENCES public.regions(region_id)
);

CREATE TABLE IF NOT EXISTS public.cities (
  city_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  name text NOT NULL,
  subregion_id bigint NOT NULL,
  CONSTRAINT cities_pkey PRIMARY KEY (city_id),
  CONSTRAINT cities_subregion_id_fkey FOREIGN KEY (subregion_id) REFERENCES public.subregions(subregion_id)
);

CREATE TABLE IF NOT EXISTS public.addresses (
  address_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
  street text NOT NULL,
  city_id bigint NOT NULL,
  postal_code text NOT NULL,
  number text NOT NULL,
  place_id uuid NOT NULL,
  CONSTRAINT addresses_pkey PRIMARY KEY (address_id),
  CONSTRAINT addresses_city_id_fkey FOREIGN KEY (city_id) REFERENCES public.cities(city_id)
);

CREATE INDEX idx_regions_country_iso ON regions(country_iso);
CREATE INDEX idx_subregions_region_id ON subregions(region_id);
CREATE INDEX idx_cities_subregion_id ON cities(subregion_id);
CREATE INDEX idx_addresses_city_id ON addresses(city_id);
CREATE INDEX idx_addresses_postal_code ON addresses(postal_code);

Configuration in application.yml (Spring Boot):

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true
    out-of-order: false

Migrations with Liquibase

Liquibase is another alternative for managing migrations. Create a file in src/main/resources/db/changelog/:

File: db.changelog-1.0.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.0.xsd">

    <changeSet id="1" author="developer">
        <comment>Create countries table</comment>
        <createTable tableName="countries" schemaName="public">
            <column name="name" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="iso_code" type="TEXT">
                <constraints primaryKey="true" primaryKeyName="countries_pkey"/>
            </column>
        </createTable>
    </changeSet>

    <changeSet id="2" author="developer">
        <comment>Create regions table</comment>
        <createTable tableName="regions" schemaName="public">
            <column name="region_id" type="BIGSERIAL">
                <constraints primaryKey="true" primaryKeyName="regions_pkey"/>
            </column>
            <column name="name" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="iso_code" type="TEXT"/>
            <column name="country_iso" type="TEXT"/>
        </createTable>
        <addForeignKeyConstraint
            baseTableName="regions" baseColumnNames="country_iso"
            referencedTableName="countries" referencedColumnNames="iso_code"
            constraintName="regions_country_iso_fkey"/>
    </changeSet>

    <changeSet id="3" author="developer">
        <comment>Create subregions table</comment>
        <createTable tableName="subregions" schemaName="public">
            <column name="subregion_id" type="BIGSERIAL">
                <constraints primaryKey="true" primaryKeyName="subregions_pkey"/>
            </column>
            <column name="name" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="code" type="TEXT"/>
            <column name="region_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint
            baseTableName="subregions" baseColumnNames="region_id"
            referencedTableName="regions" referencedColumnNames="region_id"
            constraintName="subregions_region_id_fkey"/>
    </changeSet>

    <changeSet id="4" author="developer">
        <comment>Create cities table</comment>
        <createTable tableName="cities" schemaName="public">
            <column name="city_id" type="BIGSERIAL">
                <constraints primaryKey="true" primaryKeyName="cities_pkey"/>
            </column>
            <column name="name" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="subregion_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint
            baseTableName="cities" baseColumnNames="subregion_id"
            referencedTableName="subregions" referencedColumnNames="subregion_id"
            constraintName="cities_subregion_id_fkey"/>
    </changeSet>

    <changeSet id="5" author="developer">
        <comment>Create addresses table</comment>
        <createTable tableName="addresses" schemaName="public">
            <column name="address_id" type="BIGSERIAL">
                <constraints primaryKey="true" primaryKeyName="addresses_pkey"/>
            </column>
            <column name="street" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="number" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="postal_code" type="TEXT">
                <constraints nullable="false"/>
            </column>
            <column name="city_id" type="BIGINT">
                <constraints nullable="false"/>
            </column>
            <column name="place_id" type="UUID">
                <constraints nullable="false"/>
            </column>
        </createTable>
        <addForeignKeyConstraint
            baseTableName="addresses" baseColumnNames="city_id"
            referencedTableName="cities" referencedColumnNames="city_id"
            constraintName="addresses_city_id_fkey"/>
    </changeSet>

    <changeSet id="6" author="developer">
        <comment>Create indexes to optimize queries</comment>
        <createIndex indexName="idx_regions_country_iso" tableName="regions" schemaName="public">
            <column name="country_iso"/>
        </createIndex>
        <createIndex indexName="idx_subregions_region_id" tableName="subregions" schemaName="public">
            <column name="region_id"/>
        </createIndex>
        <createIndex indexName="idx_cities_subregion_id" tableName="cities" schemaName="public">
            <column name="subregion_id"/>
        </createIndex>
        <createIndex indexName="idx_addresses_city_id" tableName="addresses" schemaName="public">
            <column name="city_id"/>
        </createIndex>
        <createIndex indexName="idx_addresses_postal_code" tableName="addresses" schemaName="public">
            <column name="postal_code"/>
        </createIndex>
    </changeSet>

</databaseChangeLog>

Configuration in application.yml (Spring Boot):

spring:
  liquibase:
    enabled: true
    change-log: classpath:db/changelog/db.changelog.xml