Najlepsze praktyki dotyczące przechowywania adresów pocztowych w bazie danych (RDBMS)?

Czy istnieją dobre odniesienia do najlepszych praktyk dotyczących przechowywania adresów pocztowych w RDBMS? Wygląda na to, że jest wiele kompromisów, które można osiągnąć, i wiele zalet i wad każdego z nich do oceny - z pewnością było to robione wielokrotnie? Może ktoś przynajmniej napisał gdzieś wyciągnął jakieś lekcje?

Przykłady kompromisów, o których mówię, obejmują przechowywanie kodu pocztowego jako liczby całkowitej w porównaniu z polem znaku, czy numer domu powinien być przechowywany jako oddzielne pole lub część wiersza adresu 1, czy numery lokali / mieszkań / itp. Powinny być znormalizowane lub po prostu przechowywane jako fragment tekstu w linii adresu 2, jak obsłużysz zip +4 (oddzielne pola lub jedno duże pole, liczba całkowita vs tekst)? itp.

W tym momencie interesują mnie przede wszystkim adresy w Stanach Zjednoczonych, ale wyobrażam sobie, że istnieją również dobre praktyki dotyczące przygotowania się na ewentualność przejścia na rynek globalny (np. Nazywanie pól odpowiednio, takich jak region, a nie stan lub kod pocztowy zamiast kodu pocztowego, itp.

W przypadku szerszego użytku międzynarodowego, jednym ze schematów do rozważenia jest ten używany przez pole adresu Drupal . Opiera się na standardzie xNAL i wydaje się, że obejmuje większość przypadków międzynarodowych. Trochę zagłębiania się w ten moduł ujawni kilka fajnych perełek do interpretacji i weryfikacji adresów w skali międzynarodowej. Posiada również ładny zestaw obszarów administracyjnych (prowincja, stan, obwód itp.) Z kodami ISO.

Oto istota schematu skopiowana ze strony modułu:

country => Country (always required, 2 character ISO code)
name_line => Full name (default name entry)
first_name => First name
last_name => Last name
organisation_name => Company
administrative_area => State / Province / Region (ISO code when available)
sub_administrative_area => County / District (unused)
locality => City / Town
dependent_locality => Dependent locality (unused)
postal_code => Postal code / ZIP Code
thoroughfare => Street address
premise => Apartment, Suite, Box number, etc.
sub_premise => Sub premise (unused)

Lekcje, których się nauczyłem:

• Nie przechowuj niczego numerycznie.
• Tam, gdzie to możliwe, przechowuj kraj i obszar administracyjny jako kody ISO.
• Kiedy nie wiesz, nie wymagaj pól. Niektóre kraje mogą nie używać pól, które uważasz za oczywiste, nawet podstawowych rzeczy, takich jak locality& thoroughfare.

Jako użytkownik „międzynarodowy” nie ma nic bardziej frustrującego niż obsługa witryny internetowej zorientowanej wyłącznie na adresy w formacie amerykańskim. Na początku jest to trochę niegrzeczne, ale staje się poważnym problemem, gdy walidacja jest również nadmierna.

Jeśli obawiasz się globalizacji, jedyną radą, jaką mam, jest swoboda. W różnych krajach obowiązują różne konwencje - w niektórych numer domu pojawia się przed nazwą ulicy, w innych po. Niektóre mają stany, niektóre regiony, niektóre hrabstwa, niektóre ich kombinacje. Tutaj, w Wielkiej Brytanii, kod pocztowy nie jest kodem pocztowym, jest to kod pocztowy zawierający zarówno litery, jak i cyfry.

Radziłbym po prostu ~ 10 wierszy ciągów o zmiennej długości wraz z osobnym polem na kod pocztowy (i uważaj, jak to opisujesz, aby poradzić sobie z narodową wrażliwością). Pozwól użytkownikowi / klientowi zdecydować, jak wpisać adresy.

Jeśli potrzebujesz wyczerpujących informacji o tym, jak inne kraje używają adresów pocztowych, oto bardzo dobry link referencyjny (Columbia University):

Frank's Compulsive Guide to Postal Addresses
Effective Addressing for International Mail

Zdecydowanie powinieneś rozważyć zapisanie numeru domu jako pola znakowego, a nie liczby, ze względu na szczególne przypadki, takie jak „półliczby” lub mój aktualny adres, czyli coś w rodzaju „129A” - ale A nie jest uważane za mieszkanie numer dla usług dostawy.

Zrobiłem to (rygorystycznie modelowałem struktury adresów w bazie danych) i nigdy bym tego więcej nie zrobił. Nie możesz sobie wyobrazić, jak szalone są wyjątki, które z reguły musisz brać pod uwagę.

Jak przez mgłę przypominam sobie pewien problem z norweskimi kodami pocztowymi (chyba), na których były wszystkie 4 pozycje, z wyjątkiem Oslo, które miało 18 lub więcej.

Jestem przekonany, że od momentu, gdy zaczęliśmy używać poprawnych geograficznie kodów pocztowych dla wszystkich naszych własnych adresów krajowych, sporo osób zaczęło narzekać, że ich poczta przyszła za późno. Okazało się, że ci ludzie mieszkali w pobliżu granicy między obszarami pocztowymi i pomimo tego, że ktoś naprawdę mieszkał na obszarze pocztowym, powiedzmy w 1600 roku, w rzeczywistości jego poczta powinna być kierowana na obszar pocztowy 1610, ponieważ w rzeczywistości był to sąsiedni obszar pocztowy. który faktycznie mu służył, więc wysłanie jego poczty do właściwego obszaru pocztowego zajęłoby jej kilka dni dłużej, z powodu niechcianej interwencji, która była wymagana we właściwym urzędzie pocztowym, aby przesłać ją do niewłaściwego obszaru pocztowego ...

(Skończyło się na tym, że zarejestrowaliśmy te osoby z adresem za granicą w kraju z kodem ISO „ZZ”).

Z pewnością powinieneś przeczytać „ Czy to dobry sposób na modelowanie informacji adresowych w relacyjnej bazie danych ”, ale twoje pytanie nie jest bezpośrednim duplikatem tego.

Z pewnością istnieje wiele wcześniej istniejących odpowiedzi (na przykład sprawdź przykładowe modele danych w DatabaseAnswers ). Wiele z istniejących wcześniej odpowiedzi jest w pewnych okolicznościach wadliwych (w ogóle nie wybiera odpowiedzi DB Answers).

Jedną z głównych kwestii do rozważenia jest zakres adresów. Jeśli Twoja baza danych musi zajmować się adresami międzynarodowymi, musisz być bardziej elastyczny niż wtedy, gdy masz do czynienia tylko z adresami w jednym kraju.

Moim zdaniem często (co nie oznacza, że zawsze ) rozsądne jest zarówno rejestrowanie „obrazu etykiety adresowej” adresu, jak i osobna analiza treści. Pozwala to radzić sobie z różnicami w umieszczaniu kodów pocztowych, na przykład między różnymi krajami. Jasne, możesz napisać analizator i program do formatowania, które zajmą się dziwactwami różnych krajów (na przykład adresy w USA mają 2 lub 3 wiersze; z kolei adresy brytyjskie mogą mieć znacznie więcej; jeden adres, do którego piszę okresowo, ma 9 wierszy). Ale łatwiej jest zlecić ludziom analizę i formatowanie oraz pozwolić DBMS po prostu przechowywać dane.

O ile nie zamierzasz wykonywać obliczeń matematycznych na numerach ulic lub kodach pocztowych / pocztowych, po prostu zachęcasz do przyszłego bólu, przechowując je jako cyfry.

Możesz zaoszczędzić kilka bajtów tu i tam i może uzyskać szybszy indeks, ale co zrobisz, gdy poczta amerykańska lub inny kraj, z którym masz do czynienia, zdecyduje o wprowadzeniu alfabetu do kodów?

Koszt miejsca na dysku będzie dużo niższy niż koszt jego późniejszej naprawy ... Czy ktoś lubi?

Dodając do tego, co @ Jonathan Leffler i @ Paul Fisher powiedział

Jeśli kiedykolwiek spodziewasz się, że do Twoich wymagań postal-codezostaną dodane adresy pocztowe w Kanadzie lub Meksyku, przechowywanie ich jako ciągu znaków jest koniecznością. Kanada ma alfanumeryczne kody pocztowe i nie pamiętam, jak wygląda Meksyk z całej mojej głowy.

Odkryłem, że najłatwiejszym sposobem jest wylistowanie wszystkich możliwych pól, od najmniejszej dyskretnej jednostki do największej. Użytkownicy będą wypełniać pola, które uznają za stosowne. Moja tabela adresów wygląda następująco:

*********************************
  Field              Type
*********************************
  address_id (PK)    int
  unit               string
  building           string        
  street             string
  city               string
  region             string
  country            string
  address_code       string
*********************************

Gdzie jest „kompromis” w przechowywaniu ZIP jako NUMBER lub VARCHAR? To tylko wybór - nie jest to kompromis, chyba że obie strony przynoszą korzyści i musisz zrezygnować z niektórych korzyści, aby uzyskać inne.

O ile suma zamków błyskawicznych nie ma żadnego znaczenia, suwaki jako liczba nie są przydatne.

To może być przesada, ale jeśli potrzebujesz rozwiązania, które działałoby w wielu krajach i musisz programowo przetwarzać części adresu:

możesz mieć obsługę adresów specyficzną dla kraju przy użyciu dwóch tabel: jednej ogólnej tabeli zawierającej 10 kolumn VARCHAR2, 10 kolumn liczbowych, innej tabeli, która odwzorowuje te pola na monity i zawiera kolumnę kraju wiążącą strukturę adresu z krajem.

Jeśli kiedykolwiek będziesz musiał zweryfikować adres lub użyć go do przetwarzania płatności kartą kredytową, będziesz potrzebować przynajmniej trochę struktury. Swobodny blok tekstu nie działa do tego zbyt dobrze.

Kod pocztowy to popularne, opcjonalne pole do sprawdzania transakcji kartą płatniczą bez użycia całego adresu. Miej więc do tego oddzielne i duże pole (co najmniej 10 znaków).

Zainspirowany odpowiedziami z bazy danych

Line1
Line2
Line3
City
Country_Province
PostalCode
CountryId
OtherDetails

Po prostu umieściłbym wszystkie pola razem w dużym polu NVARCHAR (1000), z elementem textarea, dla którego użytkownik może wpisać wartość (chyba że chcesz przeprowadzić analizę np. Kodów pocztowych). Wszystkie te dane wejściowe z linii adresu 1, wiersza 2 itd. Są tak denerwujące, jeśli masz adres, który nie pasuje do tego formatu (a wiesz, są inne kraje niż Stany Zjednoczone).