Column type and size for international country subdivisions (states, provinces, territories etc)

Question

I apologize if this is a duplication.

What column standardization would you use for storing international country subdivision data?

For example, if it was just US and Canada I believe all subdivisions have a 2-character abbreviation... which might lend to a Char(2)

This cannot possibly be sustainable internationally lest we presume there are only 1296 (A-Z, 0-9) subdivisions.

I've been unsuccessful locating an ISO list of these or even an indication of how to store them.

That's fine, I don't need to know them all now but I would like to know that there is a standard and what standard info to store as needed.

Thanks

EDIT: It appears that I can accomplish this using the ISO 3166-2 standard: http://en.wikipedia.org/wiki/ISO_3166-2

Browsable as a dataset here: http://www.commondatahub.com/live/geography/state_province_region/iso_3166_2_state_codes

Answer 1

Juat for example:

Llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch This is the name of a town in North Wales.

VARCHAR(100)

Abbreviations: There are 2-letter country code and 3-letter country code which used by UN. You can use VARCHAR(2) for 2-letter code and VARCHAR(3) for 3-letter country code.

E.G. Australia 2-letter, 3-letter and numeric code

AU   AUS   036   Australia

It all depends on how you want to save data. If you want to save 3-letter + numeric code in one column then size will be according to that and if you want to save them separate then size will be different.

To be on safe side you can use VARCHAR(10).

Answer 2

As far as I know there are no international standards because it's a national issue

Take the UK...

• Are the sub division Wales, Scotland, England, Northern Ireland? No abbreviations.
• Counties: is it "Cheshire" ("Ches.") or "Highlands and Islands" (no abbreviations
• Postal areas: Rutland is still a post county but not an official one

Your question arguably assumes a federal structure (as per Switzerland where I am) but this won't apply to many if most countries. Carrying on with Switzerland, Kanton does not feature in postal addresses or post codes either.

If there is an ISO standard, then national or local pride will annoy punters as soon as it's on your web site.

Personally, I dislike wading through a "state" dropdown on a web site. It has no meaning for me in either UK (my nationality) or my residence (Switzerland).

You may be best to stick states from US and Canada and "non US/Canada". Don't force or assume a sub-division.