How do you handle City, State and Country

Hello Folks.

I am trying to find out the best way to use the City, State and Country information stored in an Access database.

I am working on a site that has several pages that displays Location Data, City and State some to populate drop down boxes.

How do most of you handle this data? Do you make a connection to the DB every time you need it, store Country in a cookie or, gather all data and put in an array in the application or session variable. Should the table structure be designed some other way?

I would appreciate your help. My table structure is below.

tblCountry
CountryID - autonumber
Country

tblState
StateID - autonumber
CountryID -1-many
State

tblCity
CityID - autonumber
City
StateID -1-many

I use a similar structure.

You might consider using the state abbreviation as the key rather than an autonumber. This works well for US states, but if you throw in provinces or other foreign states there may not be such well-defined abbreviations.

Also there are various standardized lists of countries and country codes availabe that you can use.

Thanks for the response Doug. Where can I find these standardized lists of country codes?

From the UN, for one

http://unstats.un.org/unsd/methods/m49/m49alpha.htm

Awesome, thanks!