how to organize country data?

how do i organize country data (c开发者_如何学编程ountries, states and cities etc) in mysql?

cause every country has 3 tables: countries, states and cities.

should i have each country in separate set of tables or should i have them all in these 3 tables? if i have all of them in same tables, im afraid that the amount of rows will be huge cause i tend to have a lot of countries!

what is best practice for this?

Here's one way:

country:
country_id (INT) | country_name (VARCHAR)

state:
state_id (INT) | country_id (INT) | state_name (VARCHAR)

city:
city_id (INT) | country_id (INT) | state_id (INT) | city_name (VARCHAR)

countryTable(countryId(PK),countryName)

stateTable(stateId(PK),stateName,belongCountryId(FK))

cityTable(cityId(PK),cityTable,belongStateId(FK))

you can use "Cascade query" or Create "View" to get information from these 3 tables.