CODEWITHSUNDEEP
asp.netsql-serverdatabasedatabase-designrdbms
user311509
user311509

Reputation: 2866

How to Design "Country", "Province" and "City" Tables?

EXAMPLE 1:

xTable: xTableID, CountryName, PovinceName, CityName

EXAMPLE 2:

Country: CountryID, CountryName
Province: ProvinceID, ProvinceName
City: CityID, CityName

Question:

  1. Do you recommend me to populate country, province and city lists at application level and then use EXAMPLE 1 design? or Should i populate it at DB level as in EXAMPLE 2?

  2. City will show based on chosen province (if any) or country name. Province (if any) will show based on chosen country. Province will be only for one country and city will only be for one province/country. No one/many to many relations, so i designed it as below

NOTE: every country will have a city surely but not every country will have a province name.

Country: CountryID (PK), CountryName
Province: CountryID (PK - FK), ProvinceName
City: CountryID (PK - FK), CityName

Upvotes: 1

Views: 3381

Answers (3)

James Walford
James Walford

Reputation: 2953

You need a direct relationship from City to Country, Province to Country and City to Province. All provinces are in a country, so the Province table needs a CountryID as FK. Likewise all Cities are in a Country, so same there. As not all Countries have regions that could be called Provinces they can't be used to navigate from City to Country. ProvinceID should be in the City table, but allow null values.

Alternatively you could establish virtual provinces (e.g. Province name = No Province) for countries without Provinces and use them to relate City to Country. That would also work if there was a city which was in a country that has provinces, but wasn't itself in a province (if such a thing exists, possibly the province IS the city).

Upvotes: 2

noonand
noonand

Reputation: 2865

I have modified and used this with some success:
http://www.endswithsaurus.com/2009/07/lesson-in-address-storage.html

The person who wrote the article is a very active SO user:
BenAlabaster

Upvotes: 0

Pabuc
Pabuc

Reputation: 5638

I suggest you to add ProvinceId and CityId to tables Province and City. A province can have many cities likewise a country can have many provinces. No need to keep countryId in city, ProvinceId would be enough since it will be triggered by Province but not country.

Upvotes: 0

Related Questions