Building dependence between regional data (Street -> City -> PostalCode -> Country)

Question

I'm in the process of designing a database to contain regional data to be used inside a CRM system.

So, a user suspects that things like the city or country are automatically filled (or maybe just suggested) when he types in certain pieces of information.

Now my question: Is this feasible when using international schemes? Is it possible to get a mapping between PostalCode -> City -> Country in Germany, like I can do in the US or Japan?

How to design such a database with normalization? I already found offers like GeoNames is doing, but they're not normalized let alone referenced via FK constraints.

Answer 1

Unfortunately, you can't even derive the country from the postal code. For example, the postal code 50170 can be found in 10 different countries.

And some countries don't have postal codes.

And some countries contain other countries, not provinces (UK contains England).

For autofill, I would suggest using HTML5 geolocation or IP address geolocation to help the user fill in forms (for themselves).

Otherwise, ask them to select the country, then enter the postal code, and you can offer the available next options, depending on the country.

Geonames is not bad (though I don't like how they don't use ISO codes for primary divisions), and it's designed that way because administrative divisions are not hierarchical.