1

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.

3

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.

  • What I'm talking about was primarily suggestions, not filling in some forms the "hard" way without the user having the ability to modify the entries. So what you're saying is, that I do can suggest my users certain postalcodes or countries with a decent probability? Well, at least for ~250 countries that GeoNames is offering country and depending postalcode data for them. – SeToY Sep 11 '14 at 15:51
  • @SeToY Ya I'm not saying it's not doable, it's just complicated, and there are lots of corner-cases to handle. Assume many-to-many relationships, not hierarchical ones and you'll be okay. – Neil McGuigan Sep 11 '14 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.