CODEWITHSUNDEEP
mysqldatabasenormalization
Kevin Lee
Kevin Lee

Reputation: 1089

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

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?

Upvotes: 10

Views: 13262

Answers (3)

Joel Brown
Joel Brown

Reputation: 14398

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.

Upvotes: 1

judda
judda

Reputation: 3972

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.

Upvotes: 7

Marc B
Marc B

Reputation: 360732

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.

Upvotes: 5

Related Questions