USA vs Non-USA Addressing Methodology

[ousoonerjoe]

There are hundreds of different ways to store addresses, both Domestic (USA) and international. What methodology has worked best for you?

We are finally at the point where we can revamp the Entity tables and I'd like to get away from an "Add1, Add2...Add5" format (Mainframe Leftover). I have a few ideas in mind, but would like to see what else is out there and what has worked well for you.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[alvechurchdata]

Before you start laying out a new schema I can commend

http://www.bitboost.com/ref/international-address-formats.html#Formats

as a source of formats together with links to the various national standards.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[ousoonerjoe]

Looks like State is the "optional" value. Most others have 3 address lines, a city, Country, and Zip... just not always in that order.

AddressId
Add1
Add2
Add3
City
State
ProvRegion
Zip
CountryId

Looks like that is really all that needed. If CountryId (USA) = 1 then use State, else use ProvRegion.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[MasterRacker]

Why not combine into StateProvRegion? The meaning is already context based according to the country.

Also, to be a purist, you should use something like PostalCode instead of Zip.I believe "Zipcode" is a US only term.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[ousoonerjoe]

When programming, "Zip" is a lot less to type than PostalCode.

The reason to separate the State and ProvRegion is for the Tax Tables. I would like to keep the State empty if it is not a Taxable US State. Yes, I could use the CountryId, but for sake of keeping the data ..... I need to think about that. CountryId could easily be the CASE WHEN argument.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[MasterRacker]

Create a State lookup table. In the StateProvRegion allow picking or typing. Later when trying to determine taxability just check to see if the value in StateProvRegion is in the State table or not.

(As for Zip - sounds like you type like me.. ;-) )

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[Thargy]

joe,

since the op is about countries other than the USA, I think you've got a slight case of functional myopia. Above, you state

Looks like that is really all that needed. If CountryId (USA) = 1 then use State, else use ProvRegion.

It appears that the world consists of the USA and Canada. If you genuinely want to internationalise, please take us into account, we have counties, you know, like "the shire" in the Lord of the Rings. We live in a small country, and are therefore naturally small furry folk .

Zip is a parochial Americanism, just as me insisting on Post Code would be (if I insisted on it). I've butted my head on this one before, and eventually gave up, and settled for Postal Coding and address fields. I realised that there are only two ways to really do this. One is to have an address table with constrainted structure appropriate to each and every country of relevance, and the other is to have a "generic" (someone hold me down please, so I can stop screaming) unconstrained table that'll cope with anything. For example, I shudder to think how I would store addresses in China or Japan, since ideographs and even reduced set Kanji script are difficult for us occidentals to grapple with.

Regards

T

 

[ousoonerjoe]

I thought Canada was the 52st state of the Union, just after Puerto Rico? (just kidding).

Since the address drives our taxing, only US States are applicable to our tax practices. In other words, if CountryId = 1 then I'll need to drop into the tax settings, else, all tax fields are 0 (zero). Though the list that alvechurchdata posted does create an issue with formatting and how to handle what goes where (ie. Zip goes before City, Province/Region, Country).

There is also the aspect of having to convert the old mainframe over to a more usable format verses the free-flowing fields they are in now. I have a routine built that is based on state (if state exists, then it is a US address). However, as is the fun with any conversion from mainframe, users have been putting all sorts of info in those fields.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------