Disseny de Base de Dades Geogràfica - Esquema Relacional de Països i Ciutats

Objectiu

Crear una guia pràctica sobre com dissenyar i implementar un esquema de base de dades que modeli l’estructura geogràfica de països, regions, ciutats i adreces.

Prerequisits

Per seguir aquest article recomano tenir instal·lat:

Decisions de disseny

Principis

El disseny segueix aquests principis fonamentals:

Estructura Jeràrquica

La jerarquia geogràfica està pensada per permetre cerques eficients a qualsevol nivell:

PAÍS (countries)
  ├── REGIÓ (regions)
  │   ├── SUBREGIÓ (subregions)
  │   │   ├── CIUTAT (cities)
  │   │   │   └── ADREÇA (addresses)

Limitacions actuals

En aquest article no es contempla disposar de codis postals a les ciutats. La raó és que hi pot haver múltiples codis postals per ciutat. L’objectiu és compartir un esquema mínim i senzill vàlid.

Posteriorment, el lector pot implementar les millores o escalar el model segons necessitats.

Diagrama entitat relació

L’esquema de la base de dades del que es vol crear és el següent:

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
    }

Llegenda del Diagrama

• PK = Primary Key (Clau Primària)
• FK = Foreign Key (Clau Forana)
• ||—o{ = Relació 1 a molts (1 a N)

Taules i Relacions

Taula Countries

Emmagatzema informació de països amb codi ISO únic:

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

Camps:

• name: Nom del país (ex: “Espanya”, “Mèxic”)
• iso_code: Codi ISO 3166-1 alpha-2 de dos caràcters (ex: “ES”, “MX”). Clau primària.

Propòsit: Emmagatzemar el catàleg base de països. El codi ISO garanteix unicitat mundial.

Taula Regions

Emmagatzema regions o estats dins d’un país:

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

Camps:

• region_id: Identificador únic autoincremental
• name: Nom de la regió (ex: “Catalunya”, “Madrid”, “Bayern”)
• iso_code: Codi ISO opcional de la regió
• country_iso: Referència forana al país

Taula Subregions

Emmagatzema subdivisions dins d’una regió:

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

Camps:

• subregion_id: Identificador únic autoincremental
• name: Nom de la subregió (ex: “Província”, “Comarca”)
• code: Codi opcional de la subregió
• region_id: Referència forana a la regió

Taula Cities

Emmagatzema ciutats o municipis:

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

Camps:

• city_id: Identificador únic autoincremental
• name: Nom de la ciutat (ex: “Barcelona”, “Girona”)
• subregion_id: Referència forana a la subregió

Taula Addresses

Emmagatzema adreces postals completes:

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

Camps:

• address_id: Identificador únic autoincremental
• street: Nom del carrer (ex: “Carrer Major”)
• city_id: Referència forana a la ciutat
• postal_code: Codi postal (ex: “08001”)
• number: Número del carrer (ex: “42”, “42A”)

Patrons de Consulta Comuns

1. Obtenir adreça completa amb jerarquia 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. Cercar totes les ciutats en una regió

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 = 'Catalunya';

3. Comptar adreces per 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;

Optimització i Bones Pràctiques

Índexs Recomanats

-- Índex en el codi ISO del país
CREATE INDEX idx_regions_country_iso ON regions(country_iso);

-- Índex en region_id (JOIN freqüents)
CREATE INDEX idx_subregions_region_id ON subregions(region_id);

-- Índex en subregion_id (JOIN freqüents)
CREATE INDEX idx_cities_subregion_id ON cities(subregion_id);

-- Índex en city_id (JOIN freqüents)
CREATE INDEX idx_addresses_city_id ON addresses(city_id);

-- Índex en postal_code (cerques per codi postal de la ciutat)
CREATE INDEX idx_addresses_postal_code ON addresses(postal_code);

Consideracions de Performance

• Caché: Considera cachejar països, regions i ciutats (canvien rarament)
• Pagination: Per a llistats grans, sempre pagina els resultats
• Particionament: Per a taules >10M registres, considera particionament per país

Preguntes o suggeriments? Aquest esquema pot adaptar-se segons les teves necessitats específiques. Per exemple, podries afegir camps de zona horària, població, o codis administratius addicionals.

Scripts

Esquema Complet SQL

A continuació hi ha el SQL complet a punt per copiar i executar a la teva base de dades PostgreSQL:

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

-- Taula de Països
CREATE TABLE public.countries (
  name text NOT NULL,
  iso_code text NOT NULL,
  CONSTRAINT countries_pkey PRIMARY KEY (iso_code)
);

-- Taula de Regions
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)
);

-- Taula de Subregions
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)
);

-- Taula de Ciutats
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)
);

-- Taula de Adreces
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)
);

-- Crear índexs per optimitzar consultes
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);

Migracions amb Flyway

Flyway és una eina popular per gestionar migracions de base de dades. Per usar aquest esquema amb Flyway, crea un fitxer de migració a la carpeta src/main/resources/db/migration/:

Fitxer: 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ó a application.yml (Spring Boot):

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

Migracions amb Liquibase

Liquibase és una altra alternativa per gestionar migracions. Crea un fitxer a src/main/resources/db/changelog/:

Fitxer: 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ó a application.yml (Spring Boot):

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