ansaurus

Question

Answer 1

+1 A:

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.

oyishi 2010-03-19 03:47:43

so you mean all countries in these 3 tables with the columns you have in the parentheses? wont it get slower with queries if you got a lot of rows it has to search through? eg to get one city it has to parse through rows of ALL countries instead of only one?

never_had_a_name 2010-03-19 03:52:56

you can put all countries, states and cities on the earth into your database by using these 3 tables.

oyishi 2010-03-19 03:58:32

if you "got a lot of rows it has to search ",you can Create "View" to increase the query efficiency

oyishi 2010-03-19 04:01:24

ok thanks. then i know its best practice=)

never_had_a_name 2010-03-19 04:14:01

Answer 2

+2 A:

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)

Tom 2010-03-19 03:58:03

ansaurus

tags:

views:

answers:

how to organize country data?

related questions