How to model cities with aliases in MySQL

Question

This location (city) may have a name, as well as other aliases by which it may be known. I need to simulate this in a database.

The search can be performed using any city or any pseudonym:

For instance,

City:

• name: los angeles
• nickname: LA

When specifying the search criteria, I can specify LA or Los Angeles, and it should return the same result (for example, Hollywood).

I thought about this using a one-to-many relationship, where 1 city can have many aliases, and many aliases can appear in the same city.

When performing a search, I use the union of the city and cityAlias ​​tables to find the correct city.

Is there a better way to handle this?

EDIT: (For those who encounter the same problem / requirements and happen to come to this page) Also see My answer since I ended up using it, but the marked answer will help you identify unique cities.

Answer 1

The only thing I can add to your solution is that you can first try to find the exact match in the city tables, and if not, then join the alias. This way you can skip some connections that are quite expensive.

Another point is that this dual desktop solution may have problems with duplicate recordings. I'm not talking about the same aliases for different cities (this can be checked using a unique column), but aliases that correspond to city names. An example of these "duplicate entries" followed by a detailed explanation:

City

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 duplicate crosstabs can cause problems in the lookup table (I think you use them as a look at the “guess” that the City was actually trying to select a user when he wrote "LA"). But if the user wrote "Los Angeles", you have to decide whether to set priorities for the city or pseudonym. I know that the example I presented is a little stupid, but as a non-American citizen, I cannot provide the best examples. But there are many cities with many aliases for each ... I would not risk it :)

Checking the first table of the city will give the city priority over an equal alias name for another city. Or you can check if an alias is present in the city as the name of the city before inserting it.

That's all I can think of :)

Answer 2

Some notes:

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

To eliminate duplication of (common) names in both tables and problems that may arise, you can remove the name column from the Destination table and add the 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) ) 

If you want to find the default name, you attach Destination to DestinationDefaultAlias . To search for all aliases, you join DestinationAlias .

Answer 3

Assumption:

• La Aguardia is a random name for a city.
• LA is a pseudonym for La Aguardia

• Los Angeles is a pseudonym for New York

• For each added city, add the city name as an entry in the alias table, so we only need to search for the 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:

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

Use case 2:

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

Use case 3:

Search in New York : yields (cityID) => [2.2] = unique> [New York]