I will have international users use my database, but I don't know how the mailing system operates outside of the US.

Are the concepts "City", "State, Country and possibly "Zip" sufficient to capture any hierarchy (even if it was only 2 levels deep: (city/country)

  • 1
    You can always add another field intl_addrs_line_3 for additional information that isn't captured by city, state/province, country, postCode. – FrustratedWithFormsDesigner Dec 6 '13 at 22:23

Use a single plain text field!

Unless you're constructing a database for delivery optimization or other highly-local use, you should just go ahead and store the complete address as a text field with standard line breaks.

If there is additional information that you need, such as a zip code or street for sorting and grouping so you get a bulk mail discount, you can either store that in an additional field, or just determine it at mail-time by way of some fairly simple logic. (Or you could give each address a permanent key, and have for-discount information provided by way of other tables.)

Now, I'm not saying that you shouldn't provide some multi-part address form with city and state lists and perhaps an automatic reference to a country's postal service address API, but you didn't ask about how to construct a form for user input, you asked how to store the address in a database.

Of course, if you do that form you might want to store a JSON or XML blob so the fields can be re-populated, but that's not data that your database itself necessarily needs to care about...

|improve this answer|||||
  • ... And what 'simple logic' would allow you to determine what the zip-code is from a single blob? American zip-codes are 5 digits (9, actually), and are placed at the end of an address; Canadian zip-codes are of the form A0A 0A0, and placed at the end; Japanese zip-codes are 7 digits, and usually placed at the start: At minimum, you're going to want to store the country (although that has political issues), if only to know what the format should be. For performance reasons, anything you search/sort by should be in a separate field, at which point you should probably break it apart... – Clockwork-Muse Dec 8 '13 at 5:19
  • SELECT * FROM Customers C INNER JOIN PostalCodes P ON C.Address LIKE '%'+P.code + '%' And I already noted that you can included the distinct grouping and sorting fields, either on the same record or in a separate table with a many-to-many relation between addresses and sortable aspects. – DougM Dec 8 '13 at 17:59
  • Well, here's hoping no address has zip-code-looking data from another country! Although the larger problem is that on databases of any size, this would ignore indices... which is why, like you say, they should be in separate fields. – Clockwork-Muse Dec 8 '13 at 22:12

Like with everything, there is an international standard. In this case it's the ISO/IEC 19773 Part 08: Data structure for UPU postal data. But as usual with international standards, they are really complex. This one has 30 pages and defines about 50 possible elements which can be part of a complete postal address to cover any possible addressing scheme used somewhere in the world.

While this might be the correct way to store an address, it might not be the practicable one.

For an overview of how addresses in different countries look, you might want to take a look at the Wikipedia article about post addresses. As you see almost all countries use person, house-number, street and city (but there are a few odd cases, like Iran, where buildings have names instead of numbers or Japan, where some cities use coordinate systems). Additionally, some countries are divided into regions and a few divide them further into sub-regions. Most countries use zip-codes of different length, and you can usually expect them to carry the same information as region, subregion and city, but this information is usually still expected to verify that the zip-code is correct.

So what does that mean for the developer of a truly international address database? Be flexible. Make no field required and allow some optional general-purpose fields which can be used to represent regional quirks.

|improve this answer|||||

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.