Country/Region/City Data Model

Country: countryID, countryName

Region: regionID, countryID (FK), regionName

City: cityID, regionID (FK), cityName

1. All countries will have cities.
2. Not will all countries will have regions. Region will only carry rows that has countryID. There is only one row thats has regionID 9999 and countryID is NULL regionName is NONE.
3. Any city has no regionID, it will be referred to 9999 regionID.

---

Front End Scenario:开发者_StackOverflow中文版

1. User picks a country from drops down list
2. Region drop down gets populated
3. City drop down gets populated - only city belongs to Region

---

I can see a mistake in my design, its only valid for country with regions. If country has no regions then just populate city list and ignore that the regions list.

I could just put countryID and regionID within City table. However, i need your suggestion.

What would best solution/design that would be stable for the long run?

---

If:

All countries will have cities.

and:

Not all countries will have regions.

then:

just put countryID and regionID within City table

is the most obvious way to model it. As you suggest, it's not a hierarchical model. There are 3 separate relations:

• City in Region
• City in Country
• Region in Country

Therefore you need to capture them explicitly. However you'll need logic to ensure consistency, i.e. to prevent situations such as:

• 'Birmingham' (City) is in 'West Midlands' (Region)
• 'Birmingham' is in 'United Kingdom' (Country)
• 'West Midlands' is in 'France' (Country).

Another option would be to capture Countries and Regions in the same table, lets call it Area:

AreaID     Name             Type    ParentID
001    'UnitedKingdom'    'Country'   NULL
002    'West Midlands'    'Region'    001

Doing so removes the problem above. Each city has a single, mandatory FK to the Area table, pointing to either a 'Country' or 'Region' entry as appropriate. It also allows more flexibility in the hierarchy; e.g. it's possible to add regions within regions (if necessary), and/or add other types of area (e.g. Continent). However you'll still need to enforce some logic (e.g. can't add a Region as parent of a Country).

Either solution could work; which is best will depend on other requirements (e.g. number of writes vs. number of reads).

hth.

---

why not have a countryID in the city table as well so you can map back to either region or country depending on your need at the time

---

Well,if you put regionID in you city table,and if you don't have region,it would be null in your city table too.So no benefit.

You are asking about a 0-To-Many relationship which is a 1-to-many relationship in a special form.you allow NULL for the region part. Your previous design is feasible for the long run if in future your country 'X' have regions.

---

You have two different issues here.

• A database design issue (how to model city names)
• A user-interface design issue (how should the user enter city names)

Although those two issues interact somewhat, they really don't have much to do with each other.

For the database design issue, the "full name" of San Francisco is

San Francisco, California, United States of America

That distinguishes it from, say, San Francisco, Alabama, United States of America, which doesn't even exist.

And, going a step further, the full name of California is

California, United States of America

That distinguishes it from, say, California, Scotland, which likewise doesn't exist.

Does that suggest a useful approach to you?

---

I prefer the original option: tblCountry -> tblRegion -> tblCity. If no Region exists have Region = Country add a check box to indicate Region does not exist and ignore duplicate entry. Just a thought.