Country and currency codes in data: ISO 3166-1 and ISO 4217 without the surprises

What it is

ISO 3166-1 gives each country or territory a two-letter code (CH), a three-letter code (CHE) and a three-digit number (756). The UN M49 list (cited) publishes numeric area codes alongside the ISO alpha-3 codes, and BCP 47 (RFC 5646, cited) uses alpha-2 codes and M49 digits as region subtags. RFC 5646 also records two traps: ISO 3166-1 has "exceptionally reserved" codes, of which UK is only a synonym for the assigned GB, and AA, QM to QZ, XA to XZ and ZZ are reserved for private use. ISO 4217 gives currencies a three-letter code, a numeric code and a minor-unit exponent. The current list is published as XML by the maintenance agency SIX (cited): CHF and EUR have 2 minor units, JPY 0, BHD 3, CLF 4; precious metals, the testing code XTS and XXX ("no currency") have none; and the file carries a publication date because entries change.

Why it matters

Codes look stable and self-explanatory, so they get hard-coded. But currencies are redenominated and replaced, territories change status, and code that assumes two decimals mis-rounds yen and dinar amounts. A country code also says nothing reliable about the language, time zone or currency of a person or an account; those are separate attributes.

How to apply

• Store the alpha-2 country code and the alpha-3 currency code as uppercase text with a check constraint or a foreign key to a code table; do not store display names, translate them at the edge.
• Keep the code tables as data with valid_from and valid_to columns and a source version, and re-import from the maintenance agency's list on a schedule.
• Store the minor-unit exponent with each currency and use it for rounding and for converting between decimal amounts and minor-unit integers; read it from the list, never assume 2.
• Use numeric codes only when a partner requires them; many national currencies share the numeric code of their country (CHF and Switzerland are both 756), which is convenient in joins and unreadable in logs.
• Treat XXX (no currency involved) and XTS (testing) as explicit states in business logic, not as validation errors.

Pitfalls

UK is not an assigned country code and will fail a strict lookup. Lower-case or mixed-case codes break equality joins and unique constraints. Country codes and ISO 639 language codes overlap in spelling (de is both German and, upper-cased, Germany; be is Belarusian and Belgium) but live in different columns. A currency list is not a list of what a payment provider accepts; keep that as separate configuration.

Foreign keys and retired codes

The table a foreign key points at and the table that validates new input are not the same shape. Keep one row per code that has ever existed, with status (current, withdrawn), valid_from, valid_to and the source version; the foreign key from data rows references code alone, so historical records in NLG or AN stay loadable after each re-import. Validate new input with a check constraint or an application rule that admits only codes whose status is current at the transaction date, not through the foreign key. Re-imports then add and close rows but never delete them, and a report over old data can still translate every code it meets.