CODEWITHSUNDEEP
databasedatabase-design
Bethan
Bethan

Reputation: 971

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:

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

Can anyone clear me about this, Thanks

Upvotes: 2

Views: 1055

Answers (4)

Berserk
Berserk

Reputation: 883

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)

Upvotes: 1

AHJeebon
AHJeebon

Reputation: 1248

You can try this: those place parent_id=0 that mean state. those parent_id->parent_id=0 those are sub_city.

+------+----------------+----------------+--------+
| id   | place_name     |   parent_id    | status |
+------+----------------+----------------+--------+
|  1   | Tamilnadu      |        0       | active |
|  2   | Chennai        |        1       | active |
|  3   | Coimbatore     |        1       | active |
|  4   |subCityOfChennai|        2       | active |
|  5   | Andhra Pradesh |        0       | active |
|  6   | Chittoor       |        5       | active |
|  7   | Kadapa         |        5       | active |
|  8   | Karnool        |        5       | active |
|  9   |subCityOf Kadapa|        7       | active |
+------+----------------+----------------+--------+

Here you can use subcity feature that mean unlimited subcity facility. I have added subCityOfChennai id=4 and subCityOf Kadapa id=9.

Upvotes: 0

Faraz
Faraz

Reputation: 761

You should have at least two tables one form States and one for cities. Than you can store State ID in city table to identify what state that city belongs to.

enter image description here

Upvotes: 0

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Try this:

State Table Schema:

id, statename

City Table Schema:

id, stateid, cityname

Put cities with the corresponding state id in it. To fetch them, get the state list and cities as per state list by using stateid column

Upvotes: 0

Related Questions