Skip to content

Databases

How to Design Database to Handle Alternate City Names?

There are scenarios in many software projects where you need to handle alternate city names (city alias) entered by users. These scenario may occur when a user enters a destination while finding Hotel or Flights (or may be in any other similar cases). A travel domain portal like Expedia is a good example where a user enters a city name or area / landmark name within the city as a destination.

Note: Practically this article will be useful to RDBMS (Oracle, MySQL) developers but the syntax may be compatible only with MS SQL Server.

Now as we say customer is king so s/he can enter any popular name for a city. For example s/he can enter the following:

  • NYC for New York City (Abbreviated name)
  • Bombay for Mumbai (Mumbai’s previous name is still popular)
  • Madras for Chennai (Madras renamed to Chennai in 1996)
  • Calcutta for Kolkata.
  • PHX for Phoenix.

The following image explains the above scenario. The user enters nyc and the auto-suggestion box (auto-complete text-box) is intelligent enough to suggest New York along with other similar sounding destinations.

NewYork

Just like the above scenario a user can enter a previously known city name but you must show him the official name (or the current name). An example is shown in below image where the first result shown in the auto-suggestion box is the official city name.

Bombay

Similarly there can be scenario where you want to design your database that can handle alternate names for an area or landmark. In the following example AIIMS (All India Institute of Medical Sciences, New Delhi) is a well known landmark. The system is smart enough to guess the full name once you enter only the first three letters i.e aii of the abbreviated name.

Aiims

Solution: Database Design to Handle the Above Scenarios

Let us start with designing Country, States (State is a reserved key word in SQL Server 2008 that’s the reason the table is named States) as shown in the figure below.  The audit columns CreatedBy, CreatedDate,ModifiedBy, ModifiedDate are not necessary. You can keep them or ignore them based on your requirement. Here’s the database diagram:

Note: You can break the single city table in to two: one for storing official names and the other for storing Alternate Names.

City-Alias-DB

Record Insertion

For record insertion, you must ensure (validate) that the official name of any city is entered before the alternate names.  This is to ensure that the CityKeyId of the official name is entered in the CityId column. Though the table structure shown above can handle multiple alternate names per city, practically there may not be more than one alias for any city.

For reference, after record insertion your table should look like the following image:

Note: IsAlias data type is bit (boolean).

CityKeyId

CityName

CityId

IsAlias

1

New York

1

False

2

Phoenix

2

False

3

Chennai

3

False

4

NYC

1

True

5

Madras

3

False

6

Mumbai

6

False

7

Bombay

6

True

8

London

8

False

9

PHX

2

True

Fetching the Records

 There can be two requirements here:

  • Fetching official name of the cities of a State for dropdown list in your application.
  • Fetching the official name of the city for a suggestion box (autocomplete text-box) when the end user enters an alternate city name.

Handling the first requirement is quite simple and most of you might have guessed how to do it. Yes you can simply put a condition in where clause to exclude IsAlias=1 CityName.

For the second requirement you can use the following query (click on the image to see full size image) that will help you to fill your auto-suggestion box like the first image shown in the beginning of this article.

SQL-City-Alias

The above query is just the beginning and you can play around with it, manipulate it a bit and can use it in a stored procedure to implement a robust Area and City search that can handle any type of alternate names. Please feel free to get in touch via the comment section below if you require further help to implement this. Also, a few of you may have a better solution to the problem, please share it.

 

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)