How to model cities with aliases in MySQL

Question

A given location (city), can have a name and also other aliases by which it can be known. I need to model this in a database.

Search can be executed using either city or any of its alias:

For e.g.

City:

• name: Los Angeles
• alias: LA

When specifying the search criterion, I can either specify LA or Los Angeles, and it should return the same result (e.g. Hollywood).

I was thinking of doing it using One-To-Many relation where 1 city can have many aliases, and many aliases can map to one city.

When doing a search, I use a join of city and cityAlias table to find the correct city.

Is there a better way to deal with this?

EDIT: (For anyone who runs into the same problem/requirements and happen to comes to this page) Please see my answer as well, since I ended up using that, but the marked answer helps you identify unique cities.

Answer 1

The only thing I can add to your solution is that you can try first looking for the exact match in the city tables and if there isn't any, then joining with the alias one. That way you might skip some joins that are quite expensive.

Another thing to notice is that this double table solution might have trouble with duplicated entries. I'm not talking about same aliases for different cities (this can be checked with a unique column), but aliases matching city names. Example of these "duplicate entries" followed by detailed explanation:

Cities

ID | Name
---------
1  | Los Angeles
2  | New York

Aliases

ID | CityId | Name
------------------
1  | 1      | LA
2  | 2      | NY
3  | 2      | Los Angeles

I know this should not happen... but you know Moore's laws :) These cross-table-duplicates might give you trouble in a lookup table (I imagine you're using them as a look up to "guess" what City has actually tried to select the user when he/she wrote "LA"). But if the user wrote "Los Angeles", you'll have to decide whether to prioritize the City or the Alias. I know the example I provided is a bit silly but as a non-american citizen I can't provide better examples. But there are many cities out there with many aliases for each one... I wouldn't take a chance :)

Checking first the city table will give the city priority over an equaled named alias for other city. Or you can check whether an alias to a city is already present as a city name before inserting it.

That's all I can think of :)