국제 지리적 주소는 관계형 데이터베이스에 어떻게 저장되어야합니까?

국제 지리적 주소는 관계형 데이터베이스에 어떻게 저장되어야합니까?

---

관계형 테이블에 국제 지리적 주소를 저장하는 작업을 감안할 때 가장 유연한 스키마는 무엇입니까? 주소의 모든 부분을 고유 한 필드로 구분해야합니까, 아니면 자유 텍스트와 비슷해야합니까?

다른 형식의 주소를 다른 테이블로 분리하는 것이 의미가 있습니까? 예를 들어 USAAddress, CanadianAddress, UKAddress ...?

---

내 블로그 게시물 ( 주소 저장에 대한 강의)에서 내 생각을 요약하겠습니다 .

현재 프로젝트 [물류 회사에서 일함]에서 국제 주소를 저장하고 있습니다. 나는 데이터베이스의이 부분의 디자인에서 전 세계 주소에 대한 연구를 수행했습니다. 다양한 형식이 있습니다. 서구 세계에서는 매우 균일 한 형식을 사용하는 경향이 있습니다. 몇 가지 차이점이 있지만 대부분 다음과 같습니다.

• 번지 -숫자
• 주택 또는 건물 이름 -[VarChar-영국에서 일부 주택 / 건물은 번호가 아닌 이름으로 식별 됨]
• 거리 번호 접미사 [VarChar, 대부분의 경우 Char (1)이면 충분 함]
  • A, B 등
• 거리 이름 [VarChar]
• 거리 유형 [StreetTypes 테이블이있는 경우 VarChar 또는 Int]
  • 지금까지 영어권 세계에서 262 개의 고유 한 유형을 발견했습니다. 더 많은 유형이있을 수 있으며 Strasse, Rue 등과 같은 다른 언어도 잊지 마세요.
• 거리 방향 [VarChar (2)]
  • N, E, S, W, NE, SE, NW, SW
• 주소 유형 [AddressTypes 테이블이있는 경우 VarChar 또는 Int]
  • 우편 사서함
  • 아파트
  • 건물
  • 바닥
  • 사무실
  • 모음곡
  • 기타...
• 주소 유형 식별자 [VarChar]
  • 즉, 상자 번호, 아파트 번호, 층 번호는 아파트 번호를 기억하고 사무실에는 1A와 같은 영숫자 정보가있는 경우가 있습니다.
• 지방 자치제 [ 지방 자치제 테이블이있는 경우 VarChar 또는 Int]
  • 예를 들어, 마을 앞의 주소에 마을 / 마을이있는 경우.
• City / Town [Cities 테이블이있는 경우 VarChar 또는 Int]
• 관할 지구 [지구 테이블이있는 경우 VarChar 또는 Int]
  • 주 (미국)
  • 지방 (캐나다)
  • 연방 지구 (멕시코)
  • 카운티 (영국)
  • 기타...
• 우편 영역 [VarChar]
  • 우편 번호 (미국)
  • 우편 번호 (캐나다, 멕시코)
  • 우편 번호 (영국)
• 국가 [국가 테이블이있는 경우 VarChar 또는 Int]

이는 대부분의 국가를 포함하는 것으로 보이지만 필드 순서가 다르게 표시 될 수 있습니다. 표시 형식 목록은 http://www.bitboost.com/ref/international-address-formats.html#Formats 에서 찾을 수 있습니다 .

예를 들어 많은 국가에서 우편 번호는 도시 이름 앞에 있고 거리 번호는 거리 이름 뒤에 있습니다. 캐나다, 미국 및 영국에서는 거리 번호가 거리 이름 앞에오고 우편 번호 (또는 ZIP)는 도시 이름 뒤에옵니다.

주소를 다른 국가로 분리하는 것에 대한 귀하의 질문에 대한 답변으로, 나는 그것을 제안하지 않을 것입니다. 예를 들어보고와 같이 다른 영역에서 삶을 더 힘들게 만들 것입니다. 제가 제공 한 형식은 미국, 캐나다, 멕시코 및 영국을 문제없이 다루는 물류 데이터베이스의 모든 주소를 포함합니다. 또한 모든 유럽, 중국, 일본 및 말레이시아 주소를 포함합니다. 다른 국가에 대해서는 말할 수 없지만 아직이 필드가 지원하지 않는 국가의 주소를 저장할 필요가 없습니다.

다른 사람들이 제안한 Address1, Address2, Address3 형식을 사용하지 않는 것이 좋으며, 영숫자 문자열에서 주소 정보를 구문 분석하는 것이 처음 보이는 것처럼 간단하지 않기 때문에 특히 데이터가 올바르게 입력되지 않은 경우 많은 데이터베이스에서 볼 수 있습니다. , 잘못된 정보, 오타, 철자 오류 등으로 인해 필드를 분리하는 경우 거리 알고리즘을 사용하여 가능한 의미를 확인하고, 확률을 사용하여 우편 번호 및 번지와 거리 이름을 확인하거나,도 및 도시를 거리 이름 등과 비교하여 확인할 수 있습니다. 전체 거리 주소를 나타내는 문자열이있을 때 그 중 하나를 수행합니다. 상상력의 확장에 의한 사소한 문제가 아닙니다.

주소 데이터베이스에 대한 QA는 골칫거리입니다. 이 영역에서 생활을 단순화하는 가장 쉬운 방법은 모든 필드에 입력시 올바른 것으로 자동 확인 될 수있는 단일 정보 만 포함하도록하는 것입니다. 확률, 거리 알고리즘 및 정규식은 입력의 유효성을 확인하고 사용자에게 실수가 무엇인지에 대한 피드백을 제공하고 적절한 수정을 제안 할 수 있습니다.

주의해야 할 한 가지주의 사항은 거리 유형이기도 한 이름을 가진 도로입니다. 캐나다를 커버하는 경우 주소 1, 2를 사용하는 경우 큰 시간을 보내 게 될 토론토의 "Avenue Road"를 알아야합니다. , 3 형식. 다른 곳에서도 이런 일이 발생할 가능성이 있습니다. 비록 제가 알지 못하더라도-이 단일 인스턴스는 제가 WTF를 외치는 데 충분 했습니까?!

---

주소 형식을 과도하게 분석하지 않도록주의하십시오. 당신이 할 때, 당신은 대부분의 사용자가 작업해야하는 사양 결국 꽤 가능성이있어 주위를 효과적으로 잘못된 필드를 사용하도록 강요하거나 단지 기본 필드를 작성하고 추가 필드를 무시.

단순하게 유지하십시오.

BenAlabaster에서 언급 한 것과 같은 StreetType은 영어 또는 스페인어와 같은 격리 언어와 다른 언어로 작업을 시작할 때 문제를 일으킬 수 있습니다.

야생에서 얼마나 나쁜 일이 일어날 수 있는지 보여주기 위해 : 암스테르담의 "Henriette Roland Holststraat"는 "Henriette"+ "Roland Holst"+ "straat"로 구성되며 "Roland Holststraat"또는 "로 축약 될 수 있습니다. Roland Holststr. "또는"HRHolststr. "로 철자가 틀립니다. 또는 "Henriette Roland-Holst straat"는 날씨에 따라 다릅니다. 지구상의 각 국가에 대한 최신 거리 등록이 없으면 아무데도 갈 수 없습니다.

마지막으로, 일부 다국어 국가에서는 이름이 언어마다 다를 수 있습니다. 예를 들어, 많은 거리가 프랑스어 와 네덜란드 이름 을 모두 가지고있는 브뤼셀에서 , 수신자가 선호하는 언어에 따라 "Avenu du Port"와 "Havenlaan"이 있습니다. (Google지도는 안전을 위해 두 이름을 번갈아 표시합니다.)

여기에서 모든 종류의 영리한 트릭을 고안 할 수 있지만 영업 담당자입니다. 이것을 이해할 것입니까?

---

그것은 당신이 그것으로 무엇을하고 싶은지에 달려 있습니다.

주소가 분리되어있는 경우 다른 목적 (USPS 데이터에 대한 확인 또는 UPS / FEDEX에서 배송료 가져 오기 등)을 위해 주소를 사용하는 것이 항상 더 쉽다는 것을 알았습니다.

일반적으로 주소에 사용하는 것은 다음과 같습니다.

• 주소 라인 1
• 주소 2
• 주소 입력란 3
• 시티
• 부위
• 우편 번호
• 군
• 국가

편집에 대한 응답 : 대부분의 상황에서 사용이 보이지 않습니다. 위에 나열된 표에는 대부분의 국가 주소에 대한 충분한 필드가 있으며 충분히 일반적입니다.

---

주소

@BenAlabaster가 제공 한 훌륭한 답변과 정반대의 경우 다음과 같이 간단히 할 수 있습니다.

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

클라이언트 측 양식 레이아웃은 적절하다고 생각되는만큼 복잡 할 수 있습니다 (또는 사용자가 주소를 수동으로 입력 할 수있는 여러 줄 입력을 사용). 그런 다음 필요한 경우 주소에 줄 바꿈을 추가 할 수 있습니다.

국가

국가 테이블은 다음과 같습니다.

country_code  VARCHAR(2)
country_name  VARCHAR(255)

또한 다음 중 하나 를 가질 수 있습니다 .

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

그런 다음 다음 목록을 사용하여 국가 테이블을 디자인하십시오.

• Wikipedia : ISO 3166-1 alpha-2
• GitHub : 우편 번호가없는 국가
• GitHub : MySQL 국가 목록
• 우편 번호 정규식 목록

---

Here's an anecdote for anyone who stumbles on this question:

I speak as a person who has lived and worked on a lot of continents (Europe, Asia, North America). In my experience, and the experience of the people I work with, it has been much easier for us to use systems that do the following:

1. Provide three lines into which I will type one address. Pass these three lines on to your local postal service as I type them, verbatim. Let me use any character set I want; use UTF-8 or something better.
2. If your system has business requirements that need me to specify particular information ( such as zip code, prefecture, state, etc. ), ask for that separately. By business requirements, I mean things like analytics; these bits of information should not be shared with your local postal service ( unless I also happened to write the same information into one of the three lines from Point 1, above ).
3. Have a dropdown that asks me to specify the categorical location of address I provided in the lines of Point 1 above, perhaps Country.
4. If you must parse the information I provide in the lines of Point 1, use my answer to Point 3 to select regex. Run that regex against the information in Point 1 to parse it. Try to fill the user interface elements of Point 2 using the output from your regex. If I correct that autofilled information--use the fact that I changed it to improve your regex. Similarly, as much as possible, give me an opportunity to review and correct the output of your regex: nobody knows better what I intended to communicate than me.

Systems built like this, I find, make my life easiest. Particularly when I'm sending mail to a postal system about which your firm has virtually no functional internal knowledge.

If your firm does have internal knowledge about particular postal systems, use my selection in Point 3 to inform which view you display to me. A lot of people know what the US postal system expects on packaging; if I select US in Point 3, feel free to make the view look appropriate for a US address. If I select a country about which your firm knows nothing--display a generic three lines and let me do the rest; don't force me to use ASCII.

And let's be real here--building a complete, encyclopedic database of all global postal systems ( public and private ) is a herculean task at best, if not an impossible one. There are, for example, postal systems in which only the local, last-mile carrier really knows where an address is located. Sometimes being able to pass notes to that carrier on the packaging is extremely useful. And mapping the local knowledge of every edge case carrier into your database is indeed an impossible task.

Just ask Gödel. ( And then ask yourself if you're attempting to use an axiomatic system to model a universe of discourse, give or take some sort of arithmetic like set theory or relational algebra. )

---

Comment of Ben Alabaster's Answer: To format addresses based on country, you could use a formatting table that has the ordering of the columns for each country as separate rows.

• AddressFormat(CountryCode, FieldName, FieldOrder)

The field order can be coded to use complex grid layouts also.

There is no point in separating addresses by country. This will be chaotic as the number of countries increases and you will land in trouble if you want to find all the addresses of say, an international client. Having an Address Type suggested by Ben could also lead to ambiguities when you have an address that has both a building number and an apartment number. I could be in an apartment complex where each building has a different name. This is very common in India.

---

I use https://github.com/commerceguys/addressing library to format international addresses and they use these elements:

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

This doen't help if you want to parse the street (name, house number, ...).

Btw. if you are looking for a multilanguage country list: https://github.com/umpirsky/country-list

---

The only way is to split them to:

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

since almost every country has it's own standard for having address data, and evey country has a different format of zipcodes.
You can have a small sample of problems in my post from a similiar question.

This should not make sense to separate addresses for every country, since there are countries where you have few address conventions. Some popular conventions include not having streets in small villages, only village name and number, while streets are in larger cities’ addresses. I have learned that in Hungary’s capital – Budapest, there are few streets having the same name (you distinct them by city’s district number), while other cities does not have such addresses (someone from Hungary may actually confirm if this is true). So the total number of address formats will be numer_of_countries multiplied by number of address formats in this country… Can be done with different tables, but it will be horrible work to do.

---

I know this is an extremely old topic that is already answered, but I thought that I'd throw my two cents in as well. It all depends on what your project goals and how you expect your target users to enter addresses. Ben's suggestion will allow you to parse addresses accurately, but on the other hand could make for a longer (and possibly more frustrating) user data entry process. Stephen Wrighton's suggestion is simpler, and could be easier for users to enter addresses as a result.

I've also seen some models that simply had an "Address" column that would capture a typical street number, type, street name, unit / apartment number, etc. all in one column, while keeping City, Country, Region, etc. within other columns. Similar to Stephen's model, except Address1, Address2, and Address3 all consolidated into one column.

My opinion is that the most flexible models tend to be those that are least restrictive, depending on your interpretation of flexible.

ReferenceURL : https://stackoverflow.com/questions/1159756/how-should-international-geographical-addresses-be-stored-in-a-relational-databa