what is the best way to design a city, state, country table?

Question

I need help designing my country, city, state tables. I will provide sample data from my table so that you can help me better on my problem.

This is my country table:

Country
______
code   name
US     United States
SG     Singapore
GB     United Kingdom

This is my city table:

City
_____
id   country   city        state
1    US        Birmingham  Alabama
2    US        Auburn      Alabama
.
.
29   GB        Cambridge   NULL
30   GB        Devon       NULL

My problem is that the only country that has the state field is the US. All other cities have a null value.

My temporary solution for this is to just create a special city table for the United States, then all other countries have another city table that doesn't have the state field.

I think this will just complicate the matter, because I have two tables for cities.

How can I improve this design?

Answer 1

Why not go relational?

Country ( CountryID, CountryCode, CountryName )
Region  ( RegionID, RegionCode, RegionName, CountryID )
City    ( CityID, CityCode, CityName, RegionID )

The 'Region' name is a big more generic than State, which means it would likely make more sense everywhere.