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

There are lots of countries besides the United States that have political divisions between the national and municipal level. Australia has states, Canada has provinces, Japan has Prefectures, and so forth.

The question is how do you track this information and keep it consistent? You could have a "dummy record" at the middle level for countries that don't have one. Another way to handle this is to denormalize foreign keys to all levels down to the entity containing the address. If country and city are mandatory then their foreign keys would be not nullable whereas your state FK could be nullable.

If you go the denormalization route, you will need application logic to ensure that your foreign keys are consistent with each other.

If you go the dummy state record route, you will need application logic to ensure that dummy layers are hidden from users in the user interface.