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 :)

Answer 2

Some notes:

The DestinationAlias table does not need a surrogate key. The (idDestination, alias) (or the reverse) can serve as a PRIMARY KEY.

To eliminate the duplication of the (common) names in both tables and the problems that might occur, you can remove the name column from the Destination table and add a DestinationDefaultAlias table, having a 1:1 relationship with DestinationAlias (and an implied 1:1 relationship with Destination):

CREATE TABLE DestinationDefaultAlias
( idDestination
, alias
, PRIMARY KEY (idDestination)
, FOREIGN KEY (idDestination, alias)
    REFERENCES DestinationAlias (idDestination, alias)
) 

When you want to find the default name, you join Destination with DestinationDefaultAlias. To search all aliases, you join with DestinationAlias.

Answer 3

Assumption:

• La Aguardia is a random city name
• LA is an alias of La Aguardia

• Los Angeles is an alias of New York

• For every city added, add the city name itself as an entry to the alias table, so we only have to search on alias table.

City table:

cityId  |   Name
    1   |   Los Angeles
    2   |   New York
    3   |   La Aguardia

Alias table:

cityId  |   AliasName
    1   |   Los Angeles
    1   |   LA
    2   |   New York
    2   |   NY
    2   |   Los Angeles
    3   |   La Aguardia
    3   |   LA

Use case 1:

Search for LA: yields (cityID) => [1, 3] =unique> [Los Angeles, La Aguardia]

Use case 2:

Search for Los Angeles: yields (cityID) => [1, 2] =unique> [Los Angeles, New York]

Use case 3:

Search for New York: yields (cityID) => [2,2] =unique> [New York]