Is it a good idea to use an integer column for storing US ZIP codes in a database?

A numeric ZIP code is — in a small way — misleading. Numbers should mean something numeric. ZIP codes don’t add or subtract or participate in any numeric operations. 12309 – 12345 does not compute the distance from downtown Schenectady to my neighborhood. Granted, for ZIP codes, no one is confused. However, for other number-like … Read more

Is there common street addresses database design for all addresses of the world? [closed]

It is possible to represent addresses from lots of different countries in a standard set of fields. The basic idea of a named access route (thoroughfare) which the named or numbered buildings are located on is fairly standard, except in China sometimes. Other near universal concepts include: naming the settlement (city/town/village), which can be generically … Read more

Best practices for storing postal addresses in a database (RDBMS)?

For more international use, one schema to consider is the one used by Drupal Address Field. It’s based on the xNAL standard, and seems to cover most international cases. A bit of digging into that module will reveal some nice pearls for interpreting and validating addresses internationally. It also has a nice set of administrative … Read more

What is the ultimate postal code and zip regex?

The unicode CLDR contains the postal code regex for each country. (158 regex’s in total!) Download core.zip from http://unicode.org/Public/cldr/26.0.1/ unzip core.zip Take a look at common/supplemental/postalCodeData.xml from the unzipped content (direct content: common/supplemental/postalCodeData.xml) Google also has a web service with per-country address formatting information, including postal codes, here – http://i18napis.appspot.com/address (I found that link via … Read more