B Kumar B Kumar - 1 month ago 4
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

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)

Comments