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
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bryanmurtha
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"
Country
- CountryCode PK
- CountryName
- iso3Code
- numCode
ISO 3166-2 "Country subdivision code"
StateProvince
- CountryCode PK, FK
- StateCode PK
- StateName
UNLocal
UNLocal
- CityCode PK
- CountryCode PK, FK
- SubDivName
- Coordinates

United Nations Code for Trade and Transport Locations
UN/LOCODE.
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:
Address
- 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.
SQLUSA
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
www.sqlusa.com/freetrial2005/
SQL Server 2005 Training
cmdr_jpskywalker
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