Countries should be indexed by ISO code

[Joachim]

I recently took a look at the table of countries and found that a few were missing. Not wanting to manually cross-reference every single entry, I simply copied the ISO country codes page from the Wikipedia page, had a few editor macros generate the appropriate SQL code, and had it refill the countries table with that data.

 

Worked like a charm, except that countries are internally identified via an auto-incremented country_id field. Not surprisingly, refilling the table with the new data assigned different country ids, and I got Austrian provinces in Canada and similar niceties. It took me quite a while to sort that mess out (but it was still less work than reverting the country table and manually checking it).

 

So my request is: don't index countries with an internal ID. Index (identify) them via the two-letter ISO code. That's what ISO codes are intended for anyway ;-)

[mhormann]

I second that notion.

 

But how long can we survive with the old two-letter system (it's been introduced in 1974)? Maybe better to go for ISO 3166-1 alpha-3, since these are also used for machine-readable passports?

 

There is a list of ISO 3166-1 codes in the Wikipedia.

 

Regards,

Matthias

[Joachim]

But how long can we survive with the old two-letter system (it's been introduced in 1974)?

<{POST_SNAPBACK}>

 

Actually, for an unlimited time.

 

All sorts of things are already based on two-letter codes, so if there were a transition to three-letter codes, there's be tons of advice on the net, possibly even ready-made scripts, for doing the transition. Until then, it's better to stick with what everybody else does and use two-letter codes.

 

There are 26x26=676 two-letter codes, which is enough for several centuries to come. Of course, reasonable two-letter codes are a far more scarce resource, and I gather that there's some active work towards a three-letter standard. However, if it's alpha, it's not ready for us yet: the most important property of a primary index is that it is unique and stable, not that it is nice :D

[Joachim]

I retract the notion.

 

ISO codes are unstable - countries are added and removed from the list as political realities change.

 

However, to cope with country changes, the countries table needs an additional "valid" column, used to mark obsolete (or possibly erroneous) entries so that they aren't listed in the drop-down list when entering addresses.

Having a "valid" column is better than simply deleting or replacing the record, because archived transactions should keep the old address data.

 

Getting osCommerce to do a good job at archiving transactions may be a major task. For example, if a customer requests deletion of an address book entry, the system cannot do that if the address is used in any archived or pending transaction; at best, the system can mark the address as "invalid for new orders (and don't show it in the address book, too)".

 

There should also be a better way to handle countries - having a full list of all countries world-wide is pretty pointless if the shop ships only to EU countries. There's no good way of applying this kind of restriction though.