B Kumar B Kumar - 11 months ago 38
MySQL Question

How the database tables structure should be to map State and Cities in sql phpmyadmin?

I am very new to database and server side API's, so I have to clarify the below one with you guys.
I am designing a feature like there will be a set of states and each state have the set of cities.

I should map the group of cities to a particular state like the below example,

Ex:


  • Tamilnadu

    Chennai

    Coimbatore

    Vellore

    Salem

    Tiruppur

    Erode

  • Andhra Pradesh

    Chittoor

    Kadapa

    Karnool

    Ananthapuram

    Nellore



For this feature how the database tables structure should be look like.

Can anyone clear me about this, Thanks

Answer Source

You can do a simple database with only two tables :

First, "States" table where you have the name of the state and an unique id (primary key) :

 id:1, state:"Tamilnadu"
 id:2, state:"Andhra Pradesh"

==> id(int), state(varchar)

Second, "Cities" table with the name of the city, unique id of the city (primary key) and a reference to the "States" table (foreign key)

 id:1, city:"Chennai", stateId:1
 id:2, city:"Chittoor", stateId:2

==> id(int), city(varchar), stateId(int)