Newsgroup: it.comp.software.database
Titolo: consiglio struttura database per indirizzi

Saluti.
Devo gestire degli indirizzi postali, sia Italiani che esteri.
Ho uno schema che funziona (si fa per dire) ma vorrei dei pareri e dei
consigli per migliorarlo.
Lo schema è questo (codice non testato, l'originale è scritto con
SQLAlchemy in Python).
Non fate caso ai nomi strani, ho preso ispirazione da uno standard
OASIS chiamato xAL.
-- Tutte le nazioni
CREATE TABLE countries (
    -- Codice nazione ISO 3166-1
    country_code CHAR(2) PRIMARY KEY,
    -- Nome ISO, in italiano. Si veda:
    -- 
http://it.wikipedia.org/wiki/ISO_3166-1
    country_name VARCHAR(126) NOT NULL
);
-- Tutte le aree amministrative.
-- Al momento supporta solo l'Italia.
CREATE TABLE administrative_areas (
    -- Codice area amministrativa ISO 3166-2
    administrative_area_code CHAR(2),
    -- Codice nazione ISO 3166-1
    country_code CHAR(2) NOT NULL REFERENCES countries (country_code),
 
    -- Nome ISO, in italiano. Si veda:
    -- 
http://it.wikipedia.org/wiki/ISO_3166-2:IT
    administrative_area_name VARCHAR(126) NOT NULL,
 
    PRIMARY KEY (country_code, administrative_area_code)
);   
-- Gli indirizzi
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
 
    -- Il codice ISO della nazione
    country_code CHAR(2) NOT NULL,
    -- Codice dell'area amministrativa, ad esempio provincia per l'Italia
    -- XXX può essere NULL per indicare che non è un indirizzo italiano
    administrative_area_code CHAR(2),
    -- La località, ad esempio il comune + frazione per l'Italia
    locality VARCHAR(126),
    -- L'indirizzo, ad esempio via Nazionale
    thoroughfare VARCHAR(126),
    -- Il numero civico
    thoroughfare_number VARCHAR(20),
 
    -- Il codice di avviamento postale, dove applicabile
    postal_code VARCHAR(20),
 
    Foreign Key (country_code, administrative_area_code)
    REFERENCES administrative_areas (country_code, administrative_area_code)
)
Il primo problema è che devo usare una primary key surrogata per
identificare gli indirizzi; non ho trovato soluzioni migliori.
L'altro problema è che io ho solo le aree amministrative per l'Italia.
Per supportare gli stati esteri ho previsto che administrative_area_code
sia NULL in questo caso.
Non è una bella cosa, ma funziona.
Il problema è con le query.
Con un join tipo (pseudo codice)
SELECT * FROM addresses LEFT JOIN administrative_area LEFT JOIN countries
quando administrative_area_code è NULL, non ho nessuna informazione
dalle tabelle administrative_area e countries.
In questo caso bastarebbe fare un LEFT JOIN direttamente con countries,
almeno per ottenere il nome della nazione (che è quello che mi serve).
Esiste una soluzione generica per ottenere administrative_area_name e
country_name se i rispettivi codici sono entrambi NOT NULL, e la sola
country_name quando il primo codice è NULL?
Saluti e grazie   Manlio Perillo

 sia NULL in questo caso.
Quel null è un I-mark (attributo non applicabile). Purtroppo fanno
riferimento ad entità differenti, e probabilmente dovrebbero stare in
tabelle diverse.
Il problema dei join che descrivi dopo sembra esserne una conseguenza.
Il fatto di avere indirizzi da tutto il mondo moltiplica non poco
i problemi. Ho visto indirizzi indiani tipo "3 km north from village
xyz near city zyx". Mi sembra che ci sia uno stato che non usi neppure
i numeri civici. Hai una bella gattina da pelare :-)
bye bye

 
No, no :-).
Non sono messo così male.
L'applicazione (web) è per il mercato italiano (almeno per ora), ma devo
comunque gestire indirizzi di persone che possono risiedere all'estero
evitando, al contempo, di presentare all'utente un solo campo in cui
inserire l'indirizzo completo come piace a lui [1].
Per questo ho cercato di inserire campi generici, che possano essere
riutilizzabili mantenendosi semplici (xAL usa un modello gerarchico).
Del resto lo stesso standard xAL 
http://www.oasis-open.org/committees/ciq/ciq.html
fa notare come sia arduo organizzare le varie convenzioni di tutto il mondo.
[1] Questa è comunque una soluzione praticabile, usando un geocoder (come
quello di Google Maps) per validare ed estrarre informazioni significative
dall'indirizzo.
Tra l'altro il geocoder di Google usa proprio il formato xAL per
formattare le informazioni (per questo sono venuto a conoscenza di quello
standard).
