Zip codes do not fit well with other geographical units, so I would agree
with Rick and keep them in the Contacts table. To ensure data integrity, and
ensure that only valid city/zip code combinations can be entered in the table
you'd need a another table with columns ZipCode and CityID and an enforced
relationship between Contacts and this table on both the CityID and ZipCode
columns. I suspect most people would not go to these lengths, however.
In the UK its different as a post code here is very specific geographically
(mine refers to the 35 houses on one side of my street), so coupled with the
house name or number its possible to pin down an address. Consequently these
are all that is commonly stored in many address tables here. The Street,
City and County are determined by the post code so need not be stored in the
table, but are known via the referenced PostCodes table.
The Contacts table should not have a State column however as this is
functionally dependent on the non-key City column. Instead you should have a
Cities table with columns CityID, City and State; city names can be
duplicated so a unique CityID is necessary as the key. The Contacts table
would have a CityID foreign key column. The States table would have a State
column, and perhaps another column with the full name of each state the
abbreviated form being the key.
You'll find a demo of how to handle geographical hierarchies like this by
means of combo boxes on forms at:
http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps
The demo uses the local administrative areas here of parish, district and
county, but the principle is exactly the same.
One issue with this type of data is that of a missing link in the chain.
This arises for instance with international databases where some countries
have regional units, e.g. states in the USA, counties in the UK, départements
in France, but other small countries don't have a regional structure.
Consequently if you have Cities, Regions and Countries tables, if for cities
in those countries without a regional structure the Region was left Null,
then there would be no way of knowing which country the City is in. The
solution here is to have a row in Regions with values of a RegionID, 'N/A'
and the country name in its three columns. The RegionID foreign key column
in the Cities table would have the value for the N/A row for the country in
question.
It might also be asked why have a Countries table at all when the country
names are stored in the regions table? It’s a matter of data integrity; by
enforcing referential integrity between Countries and Regions this ensures
that only valid country names can be entered into the Regions table.
Otherwise you might have the same country entered differently, even if only
by a simple typo.
Ken Sheridan
Stafford, England