data modeling help

[oneway]

Guys

I have a Country, State and city select scenario in my project. If user selects Country then we have to make ajax call and then load the values into state dropdown and then same for State selection. So what is the best approach for this scenario to create tables in database.

I am thinking to have three different tables to retrieve country_state, State_city,city_county. Is this the better one?

 

 

[Renault]

city table with  city,state,country columns

state table with   state, country columns

country table with country  column  

all columns ki  ID and named column pettuko 

[oneway]

But after each selection(Country,state or city) we need to make three different database calls to get the values.Any suggestions to avoid this?

[mindless]

2 ways..

1 - as renault mentioned

2 - if you want to flatten the table structure then - Surrogate_Key, country_Id, cnt_NM, State_ID, State_CD, State_nm, City_Id, City_nm

[mastercheif]

7 minutes ago, mindless said:

2 ways..

1 - as renault mentioned

2 - if you want to flatten the table structure then - Surrogate_Key, country_Id, cnt_NM, State_ID, State_CD, State_nm, City_Id, City_nm

+1

[Bhai]

if you want to reduce time & calls to db go with one flat table

[MeraBharat_Mahan]

Contry Table
Country_ID    Country_Name
State Table
State_ID State_Name Country_ID(Foreign Key) 
City Table
City_ID City_name State_ID(foreign Key)