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

Clayborne

Answer 1

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.

Edit, Jun 2012.

I now live in Malta. I have neither state, county, nor Kanton. Please don't insist.

Any big cities in the UK don't normally mention county (England+Wales)/region (Scotland).

Calculated

In my particular application I need to track a country subdivision solely for internal purposes. As such, I could make up my own numerical scheme and it wouldn't really affect anything but if there is some sort of standard I might as well utilize it. – Clayborne

What if "country subdivisions" don't exist? Malta for example. It's a null concept even used internally. – Calculated

If no such subdivisions exist then I can readily leave the field null, I suppose. I did, however, find this site: commondatahub.com/live/geography/state_province_region/… What has some sort of abbreviated standard for subdivisions. Now I am searching for the rationale for their definitions. – Clayborne

....and bingo: en.wikipedia.org/wiki/ISO_3166-2 So, to answer your question about Malta... They are apparently divided into 68 local counsels: en.wikipedia.org/wiki/Local_councils_of_Malta Now.... whether or not I need to actually store this is another issue. – Clayborne

I had a look at the commondatahub. Quite amusing for the UK. Use it internally if you want, but you will offend people if you expose it or make assumptions. – Calculated

Thanks for the warning. I expect to only display the full name of areas and I hope to only require the input if it's required for postal service. – Clayborne

Matthew PK: sorry, but you're till on sticky ground. In the UK, the legal council entity can be different to the postal one: en.wikipedia.org/wiki/Postal_counties_of_the_United_Kingdom – Calculated

@gbn: Seems like I'll need to add some custom data depending on the selected country, then. Thanks – Clayborne

Answer 2

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).

Resnick

I am not talking about storing town names, or any names for that matter. I'm talking about storing a standardized abbreviation of a country subdivision (such as a state, territory etc) For example: Nevada, USA is "NV" – Clayborne

You forgot to mention that "Llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch" is pronounced "huh?" =) – Bluecoat

"Llanfairpwllgwyngyllgogerychwyrndrobwyll-llantysiliogogogoch" Pronounced: en.wikipedia.org/wiki/Llanfairpwllgwyngyll – Resnick

Your edit only addresses country codes. I am asking about country subdivision codes. – Clayborne

@Matthew, I think gbn has explained it. In my view following standard is good but in this case you are trying too hard. As I have added a town name in my answer, what would be the abbreviation of this town name? – Resnick

@Muhammad: I am not referring to town names, solely to larger divisions such as states, provinces, territories, counties etc. If no such abbreviations exist then so be it. However, I was interested in whether or not a standard for storing these exists – Clayborne

Answer 3

The main challenge for this model is that some countries can have no division (Monaco, Singapore, Vatican City) while others may have up to six levels (France).

After many years designing for this use, I've come up with the conclusion that three levels offer the most flexible solution for storing international country subdivisions. Of course this will be a ragged hierarchy and you'll need to deal with this, but it allows a very decent level of detail for all countries in the world. I have been following this structure (https://en.wikipedia.org/wiki/List_of_administrative_divisions_by_country)

In my experience, what I often do is to not to tie the table names to specific names (state, district, county, province) since this can be misleading, due to different names applied to different levels in different countries. So, the naming I generally apply is "Administrative level 1", "Administrative level 2" and "Administrative level 3", or if you want, abbreviated to admlvl1, admlvl2 and admlvl3.

I would discourage from associating these levels to the European NUTS system, since the NUTS does not match at some levels official administrative divisions.

I have added a screenshot of this structure for your reference. Administrative Divisions model

With this said, a city like Monaco-ville the data would be country:Monaco, admlvl1:Monaco (ragged, but we need to fill this level), admlvl2:Monaco (ragged, but we need to fill this level), admlvl3:Monaco (ragged, but we need to fill this level), city:Monaco-ville.

But for a city like Barcelona it would be: country:Spain, admlvl1:Catalonia, admlvl2:Barcelona, admlvl3:Barcelona (ragged, as this level does not exist), city:Barcelona.

As a final example, Dover would be country:United Kingdom, admlvl1:England, admlvl2:South East, admlvl3:Kent, city:Dover.

Of course you can extend this model to more levels, but then be aware that you'll need to fill a lot of rows with dummy values.

Ain