Domanda Come devono essere archiviati gli indirizzi geografici internazionali in un database relazionale?


Dato il compito di memorizzare gli indirizzi geografici internazionali in una tabella relazionale, qual è lo schema più flessibile? Ogni parte dell'indirizzo dovrebbe essere suddivisa nei propri campi o dovrebbe essere più simile al testo libero?

Ha senso separare indirizzi formattati in modo diverso in tabelle diverse? Ad esempio, hai un tavolo per USAAddress, CanadianAddress, UKAddress ...?


47
2017-07-21 15:01


origine


risposte:


Riassumerò i miei pensieri dal mio post sul blog - Una lezione sulla memorizzazione degli indirizzi.

Nel mio attuale progetto [lavoro per una società di logistica] memorizziamo indirizzi internazionali. Ho svolto ricerche su indirizzi in tutto il mondo nella progettazione di questa parte del database. Esistono molti formati diversi. Nel mondo occidentale tendiamo ad usare un formato abbastanza uniforme - alcune differenze ma sono principalmente:

  • Civico - Numerico
  • Nome della casa o dell'edificio - [VarChar - nel Regno Unito alcune case / edifici sono identificati per nome, non per numero]
  • Numero civico Suffisso [VarChar, anche se nella maggior parte dei casi, Char (1) sarebbe sufficiente]
    • A, B ecc
  • Nome della strada [VarChar]
  • Tipo di strada [VarChar o Int se hai una tabella StreetTypes]
    • Finora, ho trovato 262 tipi unici nel mondo di lingua inglese, probabilmente ce ne sono altri, e non dimentichiamo altre lingue, ad esempio Strasse, Rue, ecc.
  • Direzione della strada [VarChar (2)]
    • N, E, S, W, NE, SE, NW, SW
  • Tipo di Indirizzo [VarChar o Int se si dispone di una tabella AddressTypes]
    • Casella postale
    • Appartamento
    • Costruzione
    • Pavimento
    • Ufficio
    • Suite
    • eccetera...
  • Identificatore del tipo di indirizzo [VarChar]
    • Ad esempio, Numero casella, Numero appartamento, Numero piano ricorda che i numeri degli appartamenti e gli uffici a volte contengono informazioni alfanumeriche, ad esempio 1A
  • Comune locale [VarChar o Int se si dispone di una tabella Comuni]
    • Ad esempio, se il tuo villaggio / villaggio appare nell'indirizzo prima della città.
  • Città / Paese [VarChar o Int se hai una tabella delle città]
  • Distretto governativo [VarChar o Int se si dispone di una tabella Districts]
    • Stato (U.S.)
    • Provincia (Canada)
    • Distretto Federale (Messico)
    • Contea (Regno Unito)
    • eccetera...
  • Area Postale [VarChar]
    • Zip (Stati Uniti)
    • Codice postale (Canada, Messico)
    • Codice postale (Regno Unito)
  • Nazione [VarChar o Int se hai una tabella Paesi]

Questo sembra coprire la maggior parte dei paesi ma l'ordine dei campi può essere visualizzato in modo diverso. È possibile trovare un elenco di formati di visualizzazione in http://www.bitboost.com/ref/international-address-formats.html#Formats

Ad esempio, in molti paesi, il codice postale cade prima del nome della città e il numero civico ricade dopo il nome della via. In Canada, negli Stati Uniti e nel Regno Unito il numero civico precede il nome della via e il codice postale (o ZIP) viene dopo il nome della città.

In risposta alla tua domanda sulla separazione degli indirizzi in diversi paesi, non la suggerirei, renderà la vita più difficile in altre aree, ad esempio la segnalazione. Il formato che ho fornito copre tutti gli indirizzi nel nostro database logistico che copre senza problemi Stati Uniti, Canada, Messico e Regno Unito. Copre anche tutti i nostri indirizzi europei, cinesi, giapponesi e malesi. Non posso parlare per altri paesi, ma non ho ancora dovuto memorizzare un indirizzo da un paese che questi campi non supporteranno.

Non suggerisco di andare con il formato Address1, Address2, Address3 suggerito da altri e visto in molti database perché l'analisi delle informazioni di indirizzo da una stringa alfanumerica non è così semplice come potrebbe sembrare - soprattutto se i dati non sono inseriti correttamente , a causa di disinformazione, errore di battitura, errore ortografico ecc. Se si separano i campi, è possibile utilizzare gli algoritmi di distanza per verificare il significato probabile, utilizzare la probabilità per controllare il nome della via rispetto al codice postale e al numero civico o per controllare la provincia e la città dal nome della via ecc. facendo tutto questo quando hai una stringa che indica il tuo intero indirizzo. Non è una questione banale da alcun tratto dell'immaginazione.

Il QA su un database di indirizzi è un mal di testa, periodo. Il modo più semplice per semplificare la tua vita in quest'area è assicurarsi che tutti i campi contengano solo una singola informazione che può essere automaticamente verificata come corretta al momento dell'entrata. Probabilità, algoritmi di distanza ed espressioni regolari possono verificare la validità dell'inserimento e fornire un feedback all'utente su quale sia stato il loro errore e suggerire correzioni adeguate.

Un avvertimento da tenere presente sono le strade con nomi che sono anche tipi di strada: se stai coprendo il Canada, devi essere a conoscenza di "Avenue Road" a Toronto che ti farà impazzire se usi l'indirizzo 1, 2 , 3 formato. Questo probabilmente si verifica anche in altri posti, anche se non ne sono a conoscenza - questa singola istanza è stata sufficiente per urlare WTF ?!


74
2017-07-23 16:51



Fai attenzione a non sovrascrivere i formati degli indirizzi. Quando lo fai, è molto probabile che finisca con una specifica che la maggior parte degli utenti dovrà lavorare in giro, forzandoli in modo efficace a usare i campi sbagliati, o solo riempiendo i campi primari e ignorando i campi extra.

Mantieni le cose semplici.

Un StreetType come menzionato da BenAlabaster causerà problemi quando inizi a lavorare con lingue diverse dall'isolamento di lingue come l'inglese o lo spagnolo.

Per mostrarvi come le cose cattive possono scatenarsi: la "Henriette Roland Holststraat" ad Amsterdam, costruita da "Henriette" + "Roland Holst" + "straat", che può essere abbreviata con "Roland Holststraat", o " Roland Holststr. ", O errato come" HRHolststr. " o "Henriette Roland-Holst straat", a seconda del tempo. A meno che tu non abbia un registro stradale aggiornato per ogni paese sulla terra, non andrai da nessuna parte.

Infine, fai attenzione che in alcuni paesi multilingue i nomi possono essere diversi da una lingua all'altra! Ad esempio a Bruxelles dove molte strade hanno sia un francese e un nome olandese: "Avenu du Port" e "Havenlaan", a seconda della lingua preferita del destinatario. (Google Maps mostra entrambi i nomi alternativamente, solo per essere sicuri.)

Puoi provare a inventare tutti i tipi di trucchi intelligenti qui, ma sono i rappresentanti di vendita. andando a capire questo?


19
2017-07-21 15:06



Dipende da cosa vuoi fare con esso.

Ho trovato sempre più facile usare gli indirizzi per altri scopi (come la verifica contro i dati USPS o ottenere le tariffe di spedizione da UPS / FEDEX) se sono separati.

Ecco cosa uso in genere per gli indirizzi:

  • Indirizzo Linea 1
  • indirizzo 2
  • Address Line 3
  • Città
  • Regione
  • codice postale
  • contea
  • Nazione

In risposta alla modifica:  Per la maggior parte delle situazioni non vedo l'uso. La tabella che ho elencato sopra ha abbastanza campi (ed è abbastanza generica) per gli indirizzi della maggior parte dei paesi.


7
2018-02-21 16:25



Indirizzo

Come contrariamente all'eccellente risposta fornita da @BenAlabaster, potresti semplicemente avere:

address       TEXT(300)
postal_code   VARCHAR(15)
country_code  VARCHAR(2)

I layout del modulo sul lato client possono ancora essere complessi come si ritiene opportuno (oppure utilizzare un input su più righe in cui l'utente può digitare manualmente il proprio indirizzo). È quindi possibile aggiungere le interruzioni di riga nell'indirizzo ove necessario.

Nazione

La tabella del tuo paese sarà la seguente:

country_code  VARCHAR(2)
country_name  VARCHAR(255)

Inoltre, si potrebbe avere uno dei seguenti:

postal_code_required  TINYINT(1)
postal_code_regex     VARCHAR(255) NULL DEFAULT NULL

Quindi usa i seguenti elenchi per progettare la tabella del tuo paese:


4
2017-07-23 08:42



Commento della risposta di Ben Alabaster: Per formattare gli indirizzi in base al Paese, puoi utilizzare una tabella di formattazione che ha l'ordine delle colonne per ogni paese come righe separate.

  • AddressFormat (CountryCode, FieldName, FieldOrder)

L'ordine dei campi può essere codificato per utilizzare anche schemi di griglia complessi.

Non ha senso separare gli indirizzi per paese. Questo sarà caotico con l'aumento del numero di paesi e finirai nei guai se vuoi trovare tutti gli indirizzi di dire, un cliente internazionale. Avere un tipo di indirizzo suggerito da Ben potrebbe anche portare ad ambiguità quando si ha un indirizzo che ha sia un numero di edificio che un numero di appartamento. Potrei essere in un complesso di appartamenti in cui ogni edificio ha un nome diverso. Questo è molto comune in India.


1
2018-06-01 17:12



Ecco un aneddoto per tutti coloro che si imbattono in questa domanda:

Parlo come una persona che ha vissuto e lavorato in molti continenti (Europa, Asia, Nord America). Nella mia esperienza e l'esperienza delle persone con cui lavoro, è stato molto più facile per noi utilizzare sistemi che svolgono le seguenti attività:

  1. Fornire tre righe in cui digiterò un indirizzo. Passa queste tre righe al tuo servizio postale locale mentre le digito, alla lettera. Fammi usare qualsiasi set di caratteri che voglio; usa UTF-8 o qualcosa di meglio.
  2. Se il tuo sistema ha requisiti di business che mi richiedono di specificare particolari informazioni (come codice di avviamento postale, prefettura, stato, ecc.), Chiedilo separatamente. Per esigenze aziendali, intendo cose come l'analisi; questi bit di informazioni non devono essere condivisi con il servizio postale locale (a meno che non mi sia capitato di scrivere le stesse informazioni in una delle tre linee del punto 1, sopra).
  3. Avere un menu a discesa che mi chiede di specificare la posizione categoriale di indirizzo che ho fornito nelle righe del punto 1 sopra, forse Paese.
  4. Se è necessario analizzare le informazioni fornite nelle righe del punto 1, utilizzare la risposta al punto 3 per selezionare regex. Esegui la regex con le informazioni nel punto 1 per analizzarla. Prova a riempire gli elementi dell'interfaccia utente del Punto 2 usando l'output della tua espressione regolare. Se corro informazioni autofilled, utilizza il fatto che l'ho modificato per migliorare la regex. Allo stesso modo, per quanto possibile, mi dà l'opportunità di rivedere e correggere l'output della tua regex: nessuno sa meglio di cosa intendessi comunicare rispetto a me.

I sistemi costruiti in questo modo, trovo, rendono la mia vita più facile. In particolare quando invio posta a un sistema postale per il quale la tua azienda non ha praticamente alcuna conoscenza interna funzionale.

Se la tua azienda ha conoscenze interne su particolari sistemi postali, usa la mia selezione al punto 3 per informare quale vista mi mostri. Un sacco di gente sa cosa si aspetta il sistema postale degli Stati Uniti sulla confezione; se seleziono US al punto 3, sentitevi liberi di rendere la vista appropriata per un indirizzo negli Stati Uniti. Se seleziono un paese in cui la tua azienda non sa nulla, mostra tre linee generiche e lasciami fare il resto; non costringermi ad usare ASCII.

E siamo reali qui - costruire un database completo e enciclopedico di tutti i sistemi postali globali (pubblici e privati) è un compito erculeo nel migliore dei casi, se non impossibile. Esistono, ad esempio, i sistemi postali in cui solo l'operatore locale dell'ultimo miglio sa dove si trova un indirizzo. A volte essere in grado di passare le note a quel vettore sulla confezione è estremamente utile. E mappare la conoscenza locale di ogni portabagagli edge nel tuo database è davvero un compito impossibile.

Basta chiedere a Gödel. (E poi chiediti se stai tentando di usare un sistema assiomatico per modellare un universo di discorso, dare o prendere una sorta di aritmetica come teoria degli insiemi o algebra relazionale.)


1
2017-07-21 15:05



L'unico modo è dividerli a:

Name varchar,
Title varchar,
StreetAddress varchar,
StreetAddressLine2 varchar,
zipCode varchar,
City varchar,
Province varchar,
Country lookup

poiché quasi tutti i paesi hanno il proprio standard per avere i dati dell'indirizzo, e ogni paese ha un diverso formato di codici di avviamento postale.
Puoi avere un piccolo campione di problemi in il mio post da una domanda simile

Ciò non dovrebbe avere senso separare gli indirizzi per ogni paese, poiché ci sono paesi in cui si hanno poche convenzioni di indirizzo. Alcune convenzioni popolari includono il non aver strade nei piccoli villaggi, solo il nome e il numero del villaggio, mentre le strade si trovano negli indirizzi delle città più grandi. Ho imparato che nella capitale ungherese, Budapest, ci sono poche strade che hanno lo stesso nome (le distingui per numero di distretto della città), mentre altre città non hanno tali indirizzi (qualcuno dall'Ungheria potrebbe effettivamente confermare se questo è vero). Quindi il numero totale di formati di indirizzo sarà numer_of_countries moltiplicato per il numero di formati di indirizzo in questo paese ... Può essere fatto con tabelle diverse, ma sarà un lavoro orribile da fare.


0
2017-09-08 13:31



So che questo è un argomento estremamente vecchio a cui è già stata data una risposta, ma ho pensato che avrei buttato anche i miei due centesimi. Tutto dipende da quali sono gli obiettivi del tuo progetto e da come ti aspetti che i tuoi utenti target inseriscano gli indirizzi. Il suggerimento di Ben ti consentirà di analizzare accuratamente gli indirizzi, ma d'altra parte potrebbe rendere più lungo (e forse più frustrante) il processo di registrazione dei dati dell'utente. Il suggerimento di Stephen Wrighton è più semplice e potrebbe essere più facile per gli utenti inserire gli indirizzi come risultato.

Ho anche visto alcuni modelli che avevano semplicemente una colonna "Indirizzo" che catturerebbe un tipico numero civico, tipo, nome della strada, numero di unità / appartamento, ecc. Tutto in una colonna, pur mantenendo Città, Paese, Regione, ecc. all'interno di altre colonne. Simile al modello di Stephen, tranne Address1, Address2 e Address3 tutti consolidati in una colonna.

La mia opinione è che i modelli più flessibili tendano ad essere quelli meno restrittivi, a seconda della tua interpretazione di flessibile.


0
2017-08-22 15:53



Io uso https://github.com/commerceguys/addressing libreria per formattare gli indirizzi internazionali e usano questi elementi:

Country
Administrative area
Locality (City)
Dependent Locality (in: BR, CN, IR, MY, MX, NZ, PH, KR, ZA, TH)
Postal code
Sorting code
Address line 1
Address line 2
Organization
Recipient

Questo non aiuta se vuoi analizzare la strada (nome, numero civico, ...).

Btw. se stai cercando una lista di paesi multilingua: https://github.com/umpirsky/country-list


0