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?

Down and dirty solution is to download Virtuemart and use the country/state tables from that - its got 200+ counties and all the related states, then add on your cities — GDP, Jun 10 '12 at 19:07

score 7 · Answer 1 · answered Jun 10 '12 at 18:40

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.

score 5 · Answer 2 · answered Jun 10 '12 at 18:39

5

Why not a standard 3-way linked table set?

table country (
   id   int primary key,
   name varchar(255)
);

table state (
    id int primary key,
    name varchar(255),
    country_id int foreign key country (id)
);

table city (
    id int primary key,
    name varchar(255)
    state_id int foreign key state (id)
);

This'll hold up for most cases, except a few degenerate ones like Lloydminster, Saskatchewan, which straddles two provincial borders.

answered Jun 10 '12 at 18:39

Marc B

356,200
43
426
500

hello, the city table still have the state_id. what if the city don't have the state? what will happen? i really have trouble thinking in three way tables. it makes my head hurt. – Kevin Lee Jun 10 '12 at 18:48
what should i do if the state_id in the city table have no value? – Kevin Lee Jun 10 '12 at 19:10
A city which exists without a state? e.g. Singapore? – Marc B Jun 10 '12 at 19:36
yes. what if i don't want to put a value in the state_id? whats the best way to do it? if i allowed null then it will just be full of null table. – Kevin Lee Jun 10 '12 at 20:05
A bit late to the party, but came here looking for a design like this my self. When there's a NULL record I recommend to provide each table with a record that represents the NULL object. E.g. id 1 in each table has a name 'None' and then link from your city table to state with id 1. – Ben Fransen Dec 22 '15 at 14:57

score 1 · Answer 3 · answered Jun 10 '12 at 18:44

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.

yes, my plan is to normalize this. my first goal is to get all the states for US. then i will add States to Australia in the future. i need a table design that is flexible that can handle changes in the future — Kevin Lee, Jun 10 '12 at 18:50

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

3 Answers3

Linked