1

Possible Duplicate:
What is the “best” way to store international addresses in a database?

I would like the application to be global; meaning people from multiple countries (namely Australia, New Zealand, The United States and United Kingdom) can place listings. Because different countries have different formats of postcode, different classifications for areas (Australia and America have states, but places like Canada or the UK are different), how do I effectively store of all of this information?

I just can't envision in my head what the MySQL database schema for something like this would look like. I don't need to store any coordinates, just the users country, suburb/city/area and state/province so I can properly classify all of the information and display it on a classified listing.

Community
  • 1
  • 1
Dwayne Charrington
  • 6,524
  • 7
  • 41
  • 63
  • Why Did You Write Your Title Like This? – Azulflame Sep 10 '12 at 01:47
  • 2
    Azulflame there are numerous ways in which a headline for something can be written. I hardly see how asking a question about my title helps answer the question I asked. Eggyal, that looks pretty close to what I am asking, however is it just me or was there no actual answer provided, but rather just a debate on how addresses should be stored? – Dwayne Charrington Sep 10 '12 at 01:52

1 Answers1

2

In some ways, that depends on how much traffic your site is getting. Some will more than likely cringe at this, but if it is a smaller scale website and one is not expecting much traffic growth, it may not be worth normalizing your table. In that case, it may be fine just to store the country as the literal country name.

However, in most cases - especially for repeating data like a Country name - I've seen methods that require normalization. For instance, for a quick and simple approach, you can make a dedicated table for each country and assign it a integer value - usually just through auto-incrementing. In that situation, you can then assign each user the associated country id. When querying, simply join the two table together. That is usually a more efficient method.

For a very simply yet useful reference, Learning PHP, MySQL, and Javascript by O'reilly books has a similar example of normalizing tables through the above method. In a majority of cases, one will have repeating data - it's a matter of minimizing it as well as balancing efficiency and theory with practicality.

Mlagma
  • 1,240
  • 2
  • 21
  • 49
  • It's looking more likely the further I dig that normalisation is the simple solution. I was wanting to allow users to view listings via their city/area instead of country. For example I live in Brisbane, Australia in a suburb called Petrie Terrace, if I click "Australian Listings" I'll be shown listings potentially thousands of kilometres away from me that are irrelevant. Can I both normalise but have localisation flexibility too? – Dwayne Charrington Sep 10 '12 at 01:58
  • Exactly, normalization is really a method to avoid repeating data; the point is to really allow the database to run efficiently as possible. If you have a few hundred rows - there really won't be much difference is speed. But once you reach a few thousand, then you'll see a speed decrease. For your question, your queries won't make a difference because you'll simply be add JOIN statements. The database won't care. If you mean be local listings, you'll more than likely have to relate a table to your country. So say a province is within Country id 1, you'll have to identify the listings by cid_1 – Mlagma Sep 10 '12 at 02:05