Comment les adresses géographiques internationales doivent-elles être stockées dans une firebase database relationnelle?

Étant donné la tâche de stocker les adresses géographiques internationales dans une table relationnelle, quel est le schéma le plus flexible? Chaque partie de l’adresse doit-elle être divisée en champs ou doit-elle ressembler davantage à du texte libre?

Est-il judicieux de séparer les différentes adresses formatées en différentes tables? Par exemple, avoir une table pour USAAddress, CanadianAddress, UKAddress …?

Je vais résumer mes pensées à partir de mon article de blog – Une leçon de stockage d’adresses .

Sur mon projet actuel [je travaille pour une entreprise de logistique], nous stockons des adresses internationales. J’ai effectué des recherches sur des adresses dans le monde entier lors de la conception de cette partie de la firebase database. Il y a beaucoup de formats différents. Dans le monde occidental, nous avons tendance à utiliser un format assez uniforme – quelques différences, mais elles sont principalement:

  • Numéro de rue – Numérique
  • Nom de la maison ou du bâtiment – [VarChar – au Royaume-Uni, certaines maisons / bâtiments sont identifiés par leur nom, pas par leur numéro]
  • Suffixe du numéro de rue [VarChar, bien que dans la plupart des cas, Char (1) suffirait]
    • A, B etc.
  • Nom de rue [VarChar]
  • Type de rue [VarChar ou Int si vous avez une table StreetTypes]
    • Jusqu’à présent, j’ai trouvé 262 types uniques dans le monde anglophone, il y en a probablement plus, et n’oubliez pas d’autres langues, par exemple la Strasse, la rue, etc.
  • Direction de la rue [VarChar (2)]
    • N, E, S, W, NE, SE, NW, SW
  • Type d’adresse [VarChar ou Int si vous avez une table AddressTypes]
    • Boîte postale
    • Appartement
    • Bâtiment
    • Sol
    • Bureau
    • Suite
    • etc…
  • Identifiant du type d’adresse [VarChar]
    • c.-à-d. numéro de boîte, numéro d’appartement, numéro d’étage N’oubliez pas que les numéros d’appartement et les bureaux ont parfois des informations alphanumériques – comme 1A
  • Municipalité locale [VarChar ou Int si vous avez une table des municipalités]
    • Par exemple, si votre hameau / village apparaît dans l’adresse avant la ville.
  • Ville / Ville [VarChar ou Int si vous avez un tableau Villes]
  • Dissortingct dirigeant [VarChar ou Int si vous avez une table Dissortingcts]
    • Etat (US)
    • Province (Canada)
    • Dissortingct fédéral (Mexique)
    • Comté (UK)
    • etc…
  • Zone postale [VarChar]
    • Zip (US)
    • Code postal (Canada, Mexique)
    • Code postal (UK)
  • Pays [VarChar ou Int si vous avez un tableau Pays]

Cela semble couvrir la plupart des pays, mais l’ordre des champs peut être affiché différemment. Vous pouvez trouver une liste de formats d’affichage à l’ adresse http://www.bitboost.com/ref/international-address-formats.html#Formats

Par exemple, dans de nombreux pays, le code postal tombe avant le nom de la ville et le numéro de rue tombe après le nom de la rue. Au Canada, aux États-Unis et au Royaume-Uni, le numéro de rue précède le nom de la rue et le code postal (ou ZIP) vient après le nom de la ville.

En réponse à votre question sur la séparation des adresses dans différents pays, je ne le suggérerais pas, cela ne ferait que rendre la vie plus difficile dans d’autres domaines – par exemple, les rapports. Le format que j’ai fourni couvre toutes les adresses de notre firebase database logistique qui couvre sans problème les États-Unis, le Canada, le Mexique et le Royaume-Uni. Il couvre également toutes nos adresses européennes, chinoises, japonaises et malaisiennes. Je ne peux pas parler pour d’autres pays mais je n’ai pas encore eu à stocker une adresse d’un pays que ces domaines ne supporteront pas.

Je ne suggère pas d’aller avec le format Adresse1, Adresse2, Adresse3 suggéré par d’autres et vu dans de nombreuses bases de données, car l’parsing des informations d’adresse à partir d’une chaîne alphanumérique n’est pas aussi simple que possible – surtout si les données ne sont pas entrées correctement Si vous séparez vos champs, vous pouvez utiliser des algorithmes de distance pour vérifier la signification probable, utiliser la probabilité de vérifier le nom de la rue avec le code postal et le numéro de rue ou vérifier la province et la ville par nom de rue, etc. faire tout cela quand vous avez une chaîne de caractères indiquant votre adresse entière. Ce n’est pas une mince affaire par un effort d’imagination.

QA sur une firebase database d’adresses est un casse-tête, point. Le moyen le plus simple de simplifier votre vie dans ce domaine est de vous assurer que tous les champs ne contiennent qu’une seule information pouvant être vérifiée automatiquement au moment de la saisie. Les probabilités, les algorithmes de distance et les expressions régulières peuvent vérifier la validité de l’entrée et fournir à l’utilisateur un retour d’information sur son erreur et suggérer des corrections appropriées.

Une mise en garde est que les routes avec des noms qui sont aussi des types de rue – si vous couvrez le Canada, vous devez être au courant de “Avenue Road” à Toronto qui vous fera beaucoup de truc si vous utilisez l’adresse1, 2 , 3 format. Cela se produit probablement aussi ailleurs, bien que je n’en sois pas au courant – cette seule instance me suffisait pour crier WTF?!

Veillez à ne pas trop parsingr les formats d’adresse. Lorsque vous le faites, vous risquez de vous retrouver avec une spécification que la plupart des utilisateurs devront contourner , les obligeant à utiliser les champs incorrects, à ne remplir que les champs principaux et à ignorer les champs supplémentaires.

Gardez les choses simples.

Un StreetType comme mentionné par BenAlabaster posera des problèmes lorsque vous commencerez à travailler avec des langues différentes de celles utilisées pour isoler des langues comme l’anglais ou l’espagnol.

Pour vous montrer à quel point les choses peuvent mal aller: la “Henriette Roland Holststraat” à Amsterdam, construite à partir de “Henriette” + “Roland Holst” + “straat”, qui peut être abrégée en “Roland Holststraat”, ou ” Roland Holststr. “, Ou mal orthographié comme” HRHolststr. ” ou “Henriette Roland-Holst straat”, en fonction de la météo. Si vous ne disposez pas d’un registre de rue à jour pour chaque pays du monde, vous n’irez nulle part.

Et enfin, faites attention à ce que dans certains pays multilingues, les noms peuvent être différents d’une langue à une autre! Par exemple à Bruxelles où de nombreuses rues ont un nom français et un nom néerlandais: “Avenu du Port” et “Havenlaan”, selon la langue préférée du destinataire. (Google Maps affiche les deux noms en alternance, juste pour être du bon côté.)

Vous pouvez essayer de concevoir toutes sortes de trucs astucieux ici, mais ce sont les commerciaux. va comprendre cela?

Cela dépend de ce que vous voulez en faire.

J’ai toujours trouvé plus facile d’utiliser des adresses à d’autres fins (telles que la vérification des données USPS ou l’obtention des tarifs d’expédition d’UPS / FEDEX) si elles étaient séparées.

Voici ce que j’utilise généralement pour les adresses:

  • Adresse 1
  • Adresse Ligne 2
  • Ligne d’adresse 3
  • Ville
  • Région
  • code postal
  • Comté
  • Pays

En réponse à l’édition: Pour la plupart des situations, je ne vois pas l’utilisation. Le tableau que j’ai énuméré ci-dessus contient suffisamment de champs (et est suffisamment générique) pour les adresses de la plupart des pays.

Adresse

À l’inverse de l’excellente réponse de @BenAlabaster, vous pourriez simplement avoir:

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

Vos présentations de formulaire côté client peuvent toujours être aussi complexes que vous le souhaitez (ou utiliser une entrée à plusieurs lignes où l’utilisateur peut taper manuellement son adresse). Vous pouvez ensuite append les sauts de ligne dans l’adresse si nécessaire.

Pays

Votre tableau de pays ressemblerait à ceci:

 country_code VARCHAR(2) country_name VARCHAR(255) 

En outre, vous pouvez avoir l’ un des éléments suivants:

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

Utilisez ensuite les listes suivantes pour concevoir votre table de pays:

  • Wikipedia: ISO 3166-1 alpha-2
  • GitHub: pays sans code postal
  • GitHub: liste des pays MySQL
  • Liste des regex des codes postaux

Voici une anecdote pour quiconque trébuche sur cette question:

Je parle en tant que personne qui a vécu et travaillé sur de nombreux continents (Europe, Asie, Amérique du Nord). Selon mon expérience et l’expérience des personnes avec lesquelles je travaille, il nous a été beaucoup plus facile d’utiliser les systèmes suivants:

  1. Fournir trois lignes dans lesquelles je vais taper une adresse. Transmettez ces trois lignes à votre service postal local au fur et à mesure de la saisie, textuellement. Permettez-moi d’utiliser n’importe quel jeu de caractères que je veux; utilisez UTF-8 ou quelque chose de mieux.
  2. Si votre système a des exigences métier nécessitant que je spécifie des informations particulières (telles que le code postal, la préfecture, l’état, etc.), demandez-le séparément. Par exigences métier, je veux dire des choses comme l’parsing; Ces éléments d’information ne doivent pas être partagés avec votre service postal local (sauf si j’écris également les mêmes informations sur l’une des trois lignes du point 1 ci-dessus).
  3. Avoir un menu déroulant qui me demande de spécifier l’emplacement catégorique de l’adresse fournie dans les lignes du point 1 ci-dessus, peut-être Pays.
  4. Si vous devez parsingr les informations que je fournis dans les lignes du point 1, utilisez ma réponse au point 3 pour sélectionner l’expression régulière. Exécutez cette expression régulière contre les informations du point 1 pour l’parsingr. Essayez de remplir les éléments d’interface utilisateur du point 2 en utilisant la sortie de votre expression régulière. Si je corrige cette information remplie automatiquement, utilisez le fait que je l’ai changé pour améliorer votre regex. De même, autant que possible, donnez-moi l’occasion de revoir et de corriger le résultat de votre regex: personne ne sait mieux ce que j’avais l’intention de communiquer que moi.

Je trouve que les systèmes construits comme ça rendent ma vie plus facile. En particulier lorsque j’envoie du courrier à un système postal dont votre entreprise n’a pratiquement aucune connaissance fonctionnelle interne.

Si votre entreprise a des connaissances internes sur des systèmes postaux particuliers, utilisez ma sélection au point 3 pour indiquer la vue que vous affichez. Beaucoup de gens savent ce que le système postal américain attend des emballages. Si je sélectionne US dans le point 3, n’hésitez pas à faire en sorte que la vue corresponde à une adresse américaine. Si je sélectionne un pays à propos duquel votre entreprise ne sait rien – affichez trois lignes génériques et laissez-moi faire le rest; ne me force pas à utiliser ASCII.

Et soyons réels ici – la construction d’une firebase database complète et encyclopédique de tous les systèmes postaux mondiaux (publics et privés) est au mieux une tâche herculéenne, voire impossible. Il existe, par exemple, des systèmes postaux dans lesquels seul le transporteur local du dernier kilomètre sait vraiment où se trouve l’adresse. Parfois, pouvoir transmettre des notes à ce transporteur sur l’emballage est extrêmement utile. Et mapper la connaissance locale de chaque porteur de cas limite dans votre firebase database est en effet une tâche impossible.

Il suffit de demander à Gödel. (Et ensuite, demandez-vous si vous essayez d’utiliser un système axiomatique pour modéliser un univers de discours, donnez ou prenez une sorte d’arithmétique comme la théorie des ensembles ou l’algèbre relationnelle.)

Commentaire de la réponse de Ben Alabaster: Pour mettre en forme des adresses en fonction du pays, vous pouvez utiliser une table de mise en forme comportant l’ordre des colonnes de chaque pays en tant que lignes distinctes.

  • AddressFormat (CountryCode, FieldName, FieldOrder)

L’ordre des champs peut être codé pour utiliser également des dispositions de grid complexes.

Il ne sert à rien de séparer les adresses par pays. Ce sera chaotique car le nombre de pays augmente et vous aurez des problèmes si vous voulez trouver toutes les adresses, par exemple, un client international. Avoir un type d’adresse suggéré par Ben pourrait également conduire à des ambiguïtés lorsque vous avez une adresse comportant à la fois un numéro de bâtiment et un numéro d’appartement. Je pourrais être dans un complexe d’appartements où chaque bâtiment porte un nom différent. C’est très courant en Inde.

Le seul moyen est de les diviser en:

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

Étant donné que presque tous les pays ont leurs propres normes pour les données d’adresse, et que chaque pays a un format de code postal différent.
Vous pouvez avoir un petit échantillon de problèmes dans mon post à partir d’une question similaire.

Cela ne devrait pas avoir de sens pour séparer les adresses pour chaque pays, car il y a des pays où vous avez peu de conventions d’adresses. Certaines conventions populaires incluent l’absence de rues dans les petits villages, uniquement le nom et le numéro du village, tandis que les rues se trouvent dans les grandes villes. J’ai appris qu’à Budapest, la capitale hongroise, il y a peu de rues portant le même nom (vous les différenciez par numéro de quartier), alors que d’autres villes n’ont pas de telles adresses. Le nombre total de formats d’adresse sera donc multiplié par le nombre de formats d’adresses dans ce pays. Peut être fait avec des tables différentes, mais ce sera un travail horrible à faire.

Je sais que c’est un sujet extrêmement ancien auquel on a déjà répondu, mais je pensais que je mettrais aussi mes deux cents. Tout dépend des objectives de votre projet et de la manière dont vos utilisateurs cibles doivent saisir les adresses. La suggestion de Ben vous permettra d’parsingr les adresses avec précision, mais d’un autre côté, cela pourrait entraîner un processus de saisie des données utilisateur plus long (et peut-être plus frustrant). La suggestion de Stephen Wrighton est plus simple et pourrait être plus facile à saisir pour les utilisateurs.

J’ai également vu certains modèles qui avaient simplement une colonne “Adresse” qui capturerait un numéro de rue, un type, un nom de rue, un numéro d’unité / appartement, etc. dans une seule colonne, tout en conservant la ville, le pays, la région, etc. dans d’autres colonnes. Semblable au modèle de Stephen, sauf Address1, Address2 et Address3, tous regroupés dans une colonne.

Mon opinion est que les modèles les plus flexibles tendent à être ceux qui sont les moins ressortingctifs, en fonction de votre interprétation de la flexibilité.

J’utilise la bibliothèque https://github.com/commerceguys/addressing pour formater les adresses internationales et utilise ces éléments:

 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 

Cela n’aide pas si vous voulez parsingr la rue (nom, numéro de maison, …).

Btw. si vous recherchez une liste de pays multilingue: https://github.com/umpirsky/country-list