×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

USA vs Non-USA Addressing Methodology

USA vs Non-USA Addressing Methodology

USA vs Non-USA Addressing Methodology

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

RE: USA vs Non-USA Addressing Methodology

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

RE: USA vs Non-USA Addressing Methodology

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
It's never too early to begin preparing for 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

RE: USA vs Non-USA Addressing Methodology

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

RE: USA vs Non-USA Addressing Methodology

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

Jeff
It's never too early to begin preparing for 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

RE: USA vs Non-USA Addressing Methodology

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

Quote (ousoonerjoe):

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

RE: USA vs Non-USA Addressing Methodology

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close