This was a post originally submitted by daniel to address changing a little bit of the code behind countries/zones. I'd still like to pursue this, so I'm posting it back up here:

---------------

Submitted by daniel on Mon, 06/18/2007 - 09:21
daniel
Posts: 29
Joined: 05/08/2007

I'm developing a store where the client wants to be able to sell to virtually any country. At the moment Übercart needs some more work to make that happen. I have started to look into relevant standards and what else we could need to solve this once and for all. Below I've put some general information and my proposal for changes. I would like to hear what your ideas are.. What's wrong? What's missing? Ideally we could have a constructive discussion that leaves Übercart ready to go global

ISO 3166

ISO 3166 is a geographic coding standard for coding the names of countries and dependent areas, and the principal subdivisions thereof. The ISO 3166 standard is divided into three parts:

3166-1:

Codes for country and dependent area names

3166-2:

Country subdivision code defines codes for the principal subdivisions of a country or dependent area.

3166-3:

Defines codes that superseded ISO 3166-1 (for example when a country is split in two).

For Übercart only 3166-1 and 3166-2 are relevant.

3166-1 consists of:

• an official country name in English
• a three-digit, unique, identification code
• a two-letter code (alpha-2) that uniquely identifies most (but NOT all) countries
• a three-letter code (alpha-3) that uniquely identifies all countries

3166-2 consists of:

• the name of a country subdivision or dependent area
• a unique identification code consisting of two parts. The first is the alpha-2 code from iso 3166-1 and the second is an alphabetic or numerical code between one and three characters, often based on national standards. The two parts are separated by a hyphen.

Comments about ISO 3166 and Übercart

• alpha-2 codes correspond to the IATA codes used by, for example, UPS and FedEx and can be used to determine the corresponding zone when calculating shipping costs etc.
• the three-digit id is currently not used properly in all country definition files if I'm not mistaken. At least the "world zones" file available in this forum has other id:s.
• the 3166-2 subdivision is not always relevant for address/shipping purposes. Example: In Sweden zone division is not used in addresses, probably because there are relatively few cities and little risk of ambiguity. This shouldn't be a problem since the address format can be adjusted based upon country.

ISO 4217

ISO 4217 is the international standard describing three-letter codes (also known as the currency code) to define the names of currencies.

ISO 4217 consists of:

• a three-letter identification code. Most of the time constructed by combining the ISO 3166-1 alpha-2 code with the first letter of the currency name.
• a three-digit identification code that most of the time corresponds to the three-digit country identification code defined in ISO 3166-1.
• the relationship between the major and any minor currency units (ie. dollar/cent = 1/100)
• the official currency name in English
• the locations using the currency

Comments about ISO 4217 and Übercart

• At the moment currency in Übercart is limited to what is available on the "format settings" page (currency sign, decimal marker, etc.). When I developed a payment module (bankpass) I needed the currency information and added it locally for my module. I think this should be available directly in Übercart since it makes things easier both for module developers and for shop administrators.
• The major/minor currency unit relationship is important for payment/gateway modules because the amount sometimes needs to be specified in the smallest unit (an incorrect conversion could be costly for you or your customer...). It can also be used for the "number of decimal places" on the "format settings" page.

Proposal of changes/additions to Übercart

The two standards above cover most of what we need in Übercart but not everything. Mosty for the end-user's experience I'd like to see the following information as well:

• country names in the original language
• country flag
• currency names in the original language
• currency symbol and placement (pre-/post-fix notation)

With this in mind I'm thinking that the tables should look something like:

uc_country:
id - Three digits country identification code (as defined in ISO 3166-1)
name - Country name in original language
name_eng - Country name in English (as defined in ISO 3166-1)
alpha-2 - Two-letter code that uniquely identifies most (but NOT all) countries (as defined in ISO 3166-1)
alpha-3 - Three-letter code that uniquely identifies all countries (as defined in ISO 3166-1)
currency - uc_currency.num (or uc_currency.code)
enabled - Should the country be enabled in the shop interface?

uc_zone:
country - uc_countries.id
name - The official name in the original language (as defined in ISO 3166-2) (English name needed for zones?)
code - Alphanumeric code, uniquely identifying the zone (as defined in ISO 3166-2).

uc_currency:
code - Identification code (as defined in ISO 4217)
num - Identification number (as defined in ISO 4217)
exponent - Major/minor currency unit relationship (as defined in ISO 4217)
name - The official currency name in the original language
name_eng - The official currency name in English (as defined in ISO 4217)
symbol - The official currency symbol
symbol_before - Should the currency symbol be placed before or after the amount?

I also suggest a folder with flags for all countries. If possible (for copyright reasons) those that already exist on wikipedia in .svg format. The files could be named on the form: [alpha-3].svg making them easy to identify without an explicit database entry.

Links

http://en.wikipedia.org/wiki/ISO_3166-1
http://www.iso.org/iso/en/CatalogueDetailPage.CatalogueDetail?CSNUMBER=3...
http://en.wikipedia.org/wiki/ISO_3166-2
http://www.iso.org/iso/en/CatalogueDetailPage.CatalogueDetail?CSNUMBER=8...
http://en.wikipedia.org/wiki/ISO_3166-3
http://www.iso.org/iso/en/CatalogueDetailPage.CatalogueDetail?CSNUMBER=2...

---------------

Mon, 06/18/2007 - 09:29
Ryan
Posts: 1693
Joined: 09/26/2006

Thanks for taking the time to compile that and post it up here. Smiling I'm a total newbie when it comes to internationalization, so it's been some good luck that's brought us as far as we've come. What you list out makes sense to me, though the only question I have is to what extent do you believe the default database should be populated?

I don't think every single install needs to have all the zones of the world in their database, so I created the import file format that will setup the appropriate entries in the country/zone tables. Obviously using the files still works best for setting the address formats on a site that just ships to a few countries... but maybe if most countries use the same format I should just define a few presets that countries can set themselves to use instead of having an entry in the variable table for every single country.

---------------

Mon, 06/18/2007 - 11:26
daniel
Posts: 29
Joined: 05/08/2007

Personally I don't see why it would be a problem if all installations had all the information in their database. We're still talking about a relatively small table. But I guess all that doesn't really matter - if you prefer, we could auto-generate all the country files in order to use the existing system as much as possible and at the same time allow for later changes to the address format (or something else).

The only thing I really need at the moment is to add any country in _some_ way... But I thought that it might be a good idea to gather all the information that various modules are likely to use in one place instead of leaving it up to each module. If the database is not populated with all the data it becomes useless for my payment module which needs all the currency codes for example... To me it still makes sense to populate the db with everything but maybe that's just me? Someone else with a thought on the subject?

Btw. I forgot to include this link before. It's a list of most (all?) currency symbols:
http://www.xe.com/symbols.php

---------------

Tue, 07/03/2007 - 08:46
daniel
Posts: 29
Joined: 05/08/2007

While waiting for some more input on this at least I put together a new file with all ISO defined countries and zones that I found on wikipedia (took me six hours so I hope it benefits someone else as well...

The file is similar to another on this forum but has codes/names as defined in ISO 3166-1 and 3166-2.

Attachment Size
iso_countries_zones.sql_.tar.gz 43.19 KB (This download has been lost. Need daniel to repost it when he gets back on.)

---------------

Tue, 07/03/2007 - 08:57
Ryan
Posts: 1693
Joined: 09/26/2006

Wow. Thanks for bringing this back up. I got sidetracked with some PayPal and payment gateway programming, but this looks pretty incredible. At the very least we can make it officially available through a downloads section... which I hope to work on today. Wish me luck.

---------------

Tue, 07/03/2007 - 09:03
daniel
Posts: 29
Joined: 05/08/2007

good luck

I didn't want to mess to much with the table definitions but there are several things that could be improved. For example there is little point in having both "zone_id" and "zone_code" columns since they are both unique. Didn't know if some part of Ubercart depends on the "zone_id" being an integer though... Similarly mediumint(11) seems a bit overkill for "country_id" which btw shouldn't be auto_increment anymore.

I still think that we should look over the whole internationalization system so the table optimizations can wait for then...

---------------

Tue, 07/03/2007 - 10:10
CpILL
Posts: 172
Joined: 02/08/2007

Daniel,

What was the solution you came to to get all the country codes into UC? I need a similar solution but since I'm using PayPal, the number of currency's are going to be restricted to those listed by their API (see: for a list http://www.ubercart.org/comment/6690/page-PayPal)

I guess it would be intelligent for UC to make its list of available currency's based on what the payment gateway accepts. Still, if a someone in a country outside of this wants to buy (i.e. from Jamaica say) but they can pay in another currency, we still want the transaction to go though (or else be accused of suffering the third world).
--

under Too Much Information

---------------

Tue, 07/03/2007 - 11:57
daniel
Posts: 29
Joined: 05/08/2007

I still haven't taken a look at the PayPal module so I don't know how it works. At the moment however, it is up to each payment module to handle countries/zones/currencies etc. which is one of the reasons I started this thread (to at least share some common data between modules).

If the PayPal module works by sending you to their web site for payment you shouldn't have to worry at all about currencies (unless you want to show prices in multiple currencies of course). So adding countries/zones from the file in the post above could maybe be all you need (ie. allowing the customer to specify any country/zone)?

Still, if a someone in a country outside of this wants to buy[...]
Is this a problem? I thought that only the currency you want to get paid in needs to be "supported" by PayPal or am I wrong?