Best way to represent Country, State and City

[David Ball]

Hi,

What is the best way to represent Country, State and City? Should there be individual tables for each of these three fields? Or should there be one table that contains all these fields? (with the values coming from an individual table for each field?)
I would then want to use these fields, Country, State and City, in another table, tblContacts.
I would also want to have a form, frmContacts, where a user could select/ build an address from combo boxes. And the combo boxes would need to filter based on the value selected in the previous combo, (only show US states if USA is selected in country, etc). Can this be done if the fields are not in the same table?
To further complicate things I need a button next to each combo box on the form where the user can open a form an add new values (add a new Country, State and/or City).
I am after an overview of the best way of representing the Country, State and City fields before starting as I can see it getting quite confusing and very time consuming if I begin the wrong way.
Thanks very much

Dave

 

[GinaWhipp]

Hmm, what about postal code?

I keep Postal Codes and Cities on one table joined to the States table. That way when whether Users want to type the Postal Code (or City) and have the balance filled or just select the State and fill in the balance I can be flexible. The States table has a flag to indicate if International or not, so it's really States and Countries.

I say that to say, not sure there is a true and tried way. It may depend on what kind of Users you have.

 

[The_Doc_Man]

re: Country, State, and City...

Should there be individual tables for each of these three fields? Or should there be one table that contains all these fields?

The general principles of normalization suggest that they should be together when referring to a particular person's address since in that case, they would depend on the prime key that selected the person. The "key dependency" issue would require them to be together.

For lookup purposes, as a matter of practicality, you should have them together in one table as a set of cascading combo boxes on the Country, then State, then City (probably in that order). After all, there IS a geographic relationship among them.

Having them kept separately allows you to create an impossible address. For instance, if they are separate, you could create an address containing Yscloskey, Iowa, USA when in fact it should be Yscloskey, Louisiana, USA. Using a cascading combo on three successive fields of one table, you would be able to select a nation in a combo box. If you selected the USA, then the state combo would then only give you states of the USA. And if you then selected Iowa, you couldn't select Yscloskey since it's not a town in Iowa. (But it IS a legit town in Louisiana.)

Take advantage of that relationship. Makes your life easier when you write things to assure, or at least assist with, the accuracy of your address selections.

 

[David Ball]

Thanks very much

 

[Gasman]

Doc_Man,


For my benefit please.
Surely the same could be applied with 3 separate tables and cascading combos?
I only ask as my first instinct would have been to have 3 tables.?


TIA

re: Country, State, and City...
Having them kept separately allows you to create an impossible address. For instance, if they are separate, you could create an address containing Yscloskey, Iowa, USA when in fact it should be Yscloskey, Louisiana, USA. Using a cascading combo on three successive fields of one table, you would be able to select a nation in a combo box. If you selected the USA, then the state combo would then only give you states of the USA. And if you then selected Iowa, you couldn't select Yscloskey since it's not a town in Iowa. (But it IS a legit town in Louisiana.)

 

[Jeffr.Lipton]

If you are using multiple countries, you might want to use the one of the ISO 3166-1 codes (see https://en.wikipedia.org/wiki/ISO_3166-1 for more information, then have a seperate look-up table that links each country to its code. That way if a country changes its name, you only have to update your lookup table.

 

[The_Doc_Man]

Gasman, my opinion (emphasized as such) is that you COULD have a list of all valid city, state, country combinations in a single table such that when you had selected ALL of those things together, a fourth field (the PK of the table) would be all you need to specify all three fields in the later usage thereof. There would be nothing to stop you from using cascading combo boxes in such a setup even though when done, what you want is the PK of the CityStateCountry table.