Diseño de Base de Datos Geográfica - Esquema Relacional de Países y Ciudades

Objetivo

Crear una guía práctica sobre cómo diseñar e implementar un esquema de base de datos que modele la estructura geográfica de países, regiones, ciudades y direcciones.

Prerequisitos

Para seguir este artículo recomiendo tener instalado:

Herramienta/Paso	Descripción
PostgreSQL 14+	Descargar PostgreSQL
pgAdmin o DBeaver	DBeaver Community
Cliente SQL (psql)	Incluido en PostgreSQL

Decisiones de diseño

Principios

El diseño sigue estos principios fundamentales:

Principio	Descripción
Normalización	Estructura en 3ª forma normal (3NF) para evitar redundancia de datos
Relaciones claras	Jerarquía explícita: País → Región → Subregión → Ciudad → Dirección
Integridad	Constraints a nivel de base de datos para validar relaciones
Flexibilidad	Campos mínimos para que luego puedas añadir más campos

Estructura Jerárquica

La jerarquía geográfica está pensada para permitir búsquedas eficientes a cualquier nivel:

PAÍS (countries)
  ├── REGIÓN (regions)
  │   ├── SUBREGIÓN (subregions)
  │   │   ├── CIUDAD (cities)
  │   │   │   └── DIRECCIÓN (addresses)

Limitaciones actuales

En este artículo no se contempla disponer de códigos postales en las ciudades. La razón es que puede haber mútliples códigos postales por ciudad. El objetivo es compartir un esquema mínimo y sencillo válido.

Posteriormente, el lector puede implementar las mejoras o escalar el modelo según necesidades.

Diagrama entidad relación

El esquema de la base de datos de lo que se quiere crear es el siguiente:

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
    }

Leyenda del Diagrama

PK = Primary Key (Clave Primaria)
FK = Foreign Key (Clave Foránea)
||—o{ = Relación 1 a muchos (1 a N)

Tablas y Relaciones

Tabla Countries

Almacena información de países con código ISO único:

countries.sql

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

Campos:

name: Nombre del país (ej: “España”, “México”)
iso_code: Código ISO 3166-1 alpha-2 de dos caracteres (ej: “ES”, “MX”). Clave primaria.

Propósito: Almacenar el catálogo base de países. El código ISO garantiza unicidad mundial.

Tabla Regions

Almacena regiones o estados dentro de un país:

regions.sql

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

Campos:

region_id: Identificador único autoincremental
name: Nombre de la región (ej: “Comunidad de Madrid”, “Ciudad de México”, “Bayern”)
iso_code: Código ISO opcional de la región
country_iso: Referencia foránea al país

Tabla Subregions

Almacena subdivisiones dentro de una región:

subregions.sql

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

Campos:

subregion_id: Identificador único autoincremental
name: Nombre de la subregión (ej: “Provincia”, “Condado”)
code: Código opcional de la subregión
region_id: Referencia foránea a la región

Tabla Cities

Almacena ciudades o municipios:

cities.sql

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

Campos:

city_id: Identificador único autoincremental
name: Nombre de la ciudad (ej: “Madrid”, “Barcelona”)
subregion_id: Referencia foránea a la subregión

Tabla Addresses

Almacena direcciones postales completas:

addresses.sql

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

Campos:

address_id: Identificador único autoincremental
street: Nombre de la calle (ej: “Calle Gran Vía”)
city_id: Referencia foránea a la ciudad
postal_code: Código postal (ej: “28001”)
number: Número de la calle (ej: “42”, “42A”)

Patrones de Consulta Comunes

1. Obtener dirección completa con jerarquía geográfica

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. Buscar todas las ciudades en una región

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 = 'Comunidad de Madrid';

3. Contar direcciones por país

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;

Optimización y Mejores Prácticas

Índices Recomendados

indices.sql

-- Índice en el código ISO del país
CREATE INDEX idx_regions_country_iso ON regions(country_iso);

-- Índice en region_id (JOIN frecuentes)
CREATE INDEX idx_subregions_region_id ON subregions(region_id);

-- Índice en subregion_id (JOIN frecuentes)
CREATE INDEX idx_cities_subregion_id ON cities(subregion_id);

-- Índice en city_id (JOIN frecuentes)
CREATE INDEX idx_addresses_city_id ON addresses(city_id);

-- Índice en postal_code (búsquedas por código postal de la ciudad)
CREATE INDEX idx_addresses_postal_code ON addresses(postal_code);

Consideraciones de Performance

Caché: Considera cachear países, regiones y ciudades (cambian raramente)
Pagination: Para listados grandes, siempre pagina los resultados
Particionamiento: Para tablas >10M registros, considera particionamiento por país

¿Preguntas o sugerencias? Este esquema puede adaptarse según tus necesidades específicas. Por ejemplo, podrías agregar campos de zona horaria, población, o códigos administrativos adicionales.

Scripts

Esquema Completo SQL

A continuación está el SQL completo listo para copiar y ejecutar en tu base de datos PostgreSQL:

-- Crear esquema si no existe
CREATE SCHEMA IF NOT EXISTS public;

-- Tabla de Países
CREATE TABLE public.countries (
  name text NOT NULL,
  iso_code text NOT NULL,
  CONSTRAINT countries_pkey PRIMARY KEY (iso_code)
);

-- Tabla de Regiones
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)
);

-- Tabla de Subregiones
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)
);

-- Tabla de Ciudades
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)
);

-- Tabla de Direcciones
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_place_id_fkey FOREIGN KEY (place_id) REFERENCES public.places(place_id),
  CONSTRAINT addresses_city_id_fkey FOREIGN KEY (city_id) REFERENCES public.cities(city_id)
);

-- Crear índices para optimizar consultas
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);

Migraciones con Flyway

Flyway es una herramienta popular para gestionar migraciones de base de datos. Para usar este esquema con Flyway, crea un archivo de migración en la carpeta src/main/resources/db/migration/:

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

Configuración en application.yml (Spring Boot):

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

Migraciones con Liquibase

Liquibase es otra alternativa para gestionar migraciones. Crea un archivo en src/main/resources/db/changelog/:

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

Configuración en application.yml (Spring Boot):

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