City and Provinces

[rasinc]

I've read a few posts that deal with address information but am not getting a
consistent view from the experts here so I thought I would ask the question.

I am creating a database that will contain address information. Many of the
cities and provinces will be the same. Should I have a separate tblCity and
tblProv tables with the tblCity linking to the tblProv table?

I am looking at ease of data entry also. I would like the users to be able
to easily select the information. Should they just select from a list and if
not in the list, add it. Ideally a combo box will include an city and
province combination in the file for use in any of three possible tables that
will include address information. Or should my users select the province and
this displays a list of cities (this was suggested by a user). Or should
they select a city and the province show up in the next combo? If the
province is wrong then they change it to one that is correct? eg. Toronto
can be in Ontario or PEI.

TIA rasinc

 

[tina]

from a tables standpoint, as long as all you're dealing with are *names* of
cities only (and NO other data that describes a city), recommend a
tblProvinces, a tblCities, and a tblProvinceCities. since one province may
have many cities, and one city *name* may appear in multiple provinces,
there is a many-to-many relationship between those two entities.
tblProvinceCities provides the link between the two with the following
one-to-many relationships, as

tblProvinces.ProvinceID 1:n tblProvincesCities.ProvinceID
tblCities.CityID 1:n tblProvinceCities.CityID

as you've seen, there are several approaches to this, from a data entry
standpoint. i'd recommend that you listen to your user(s). table design is
based on relational data principles, but form design should support the
process that the user follows. so if it makes more sense from the users'
standpoint to choose from a list of provinces first, then choose from a
filtered list of cities in that province - that's what you should give them.

one word of caution: be careful in letting your user(s) update the table
underlying a combo box control's RowSource. you should set it up so that the
user must put a modicum of thought into adding new cities; make it too slick
and easy, and you'll end up with a mess on your hands - incomplete and
misspelled entries that proliferate through the data records. from the
standpoint of aggregating data, that's a disaster - and guess who will have
to go into the tables and clean it up, again and again!

hth