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

EXAMPLE 1:

xTable:开发者_Go百科 xTableID, CountryName, PovinceName, CityName

• OR

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

---

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

---

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.

---

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