Best way to represent Country, State and City (1 Viewer)

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

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.

There are a number of ways to approach this. I also like to use zip code but because zip code might not be available, you have to support some other method.

Look at your set of data. Do you have to support every city in every state/province in every country in the world? Or is there a limit to your geographical universe?

A lookup to city that shows state and country would be user friendly but be careful if you decide to add entries on the fly. You don't want people to add typos because they don't know how to spell. I almost never allow entries to be added on the fly for this reason. However, I can understand that you might need to do this so you might consider using a SoundEX check for duplicates as you are adding new entries.

When you use the one table method, for the selection, I would store the ID for that row rather than separate city, state, and country IDs. Again, this is going to depend on how rigid your data entry rules are. For example, if you allow country without city and state, then you must use three separate combos which you can sync. If all three are required, then one list will provide simpler data entry.

Talk to the users and see what they think they need. You might create a sample form showing several methods to see what makes sense to them.

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?

Click to expand...

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.

Thanks very much

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

The_Doc_Man said:

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

Click to expand...

I would keep a country table and a state/province table with a FK to tblCountry. However, the city table is what I would use for all selections. When your city form is used to build a new row, your combos are based on the state/province table so that only states/provinces already assigned to a specific country can be selected for that country.

Three independent combos will allow you to create invalid combinations on the data entry form. It's not like countries, states, and cities spring up out of nowhere so it isn't appropriate to allow them to be added to lookup tables on the fly. Someone with a little geographic savvy (not my daughter for example) should keep the base tables in order. When doing data entry, if you want to use three cascading combos, that's fine but the point is that all three should get their data from the cities table so no invalid combination can be selected.

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.

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.