UNLocale ISO & 3166 18i & 10l

Last Post 25 Apr 2006 12:13 PM by cmdr_jpskywalker. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
New Member
New Member

11 Jun 2005 03:19 AM
I'm kind of perplexed on how to deal with this. I keep looking around and I find no coherent standard on how to represent address information. I understand that certain countries like Portugal don't have a state/province concept in thier subdivision scheme. The UK is just a nightmare. This database will be an aggregator of info in other DBs that I have no control over and so I wanted to follow the accepted standards. I wanted to have a setup like this:
ISO 3166-1 "Country codes"
- CountryCode PK
- CountryName
- iso3Code
- numCode
ISO 3166-2 "Country subdivision code"
- CountryCode PK, FK
- StateCode PK
- StateName
- CityCode PK
- CountryCode PK, FK
- SubDivName
- Coordinates

United Nations Code for Trade and Transport Locations
The reason why city points back to country rather then state
is because of the aboved mentioned standard. It only makes use of the subdivisions codes when there are two places with the same name, like Newark, OH and Newark, NJ. Otherwise it's just Newark, USA. The rational behind this is that there are some countries that have no sub-divisions, only cities. So I stricter hierchary of child-parent-grandparent wouldn't work in this situation.

My Address table would look like this:
- AddressID
- AddrLine1
- AddrLine2
- CityCode FK
- Statecode FK
- AddressTypeID FK

I'd imagine this is very common problem. I was wondering what approach others took? How it can be improved.
New Member
New Member

30 Dec 2005 03:36 PM
True small countries don't have subdivisions. Only cities or villages.

Also true, in Japan the don't even street names (never mind numbers) in some areas.

I've seen the State/Province sometimes calles State/Province/Region.

Kalman Toth
SQL Server 2005 Training
New Member
New Member

25 Apr 2006 12:13 PM
use this with unicode (NVARCHAR)

id INT
addr1 (2048)
addr2 (2048)
apt_suite_nbr (10)
town_village (100)
city (150)
state (50)
region_province (80)
localzipcode (20)
country (80)
countryzipcode (30)
You are not authorized to post a reply.

Acceptable Use Policy