如何在 MySQL 中使用别名对城市进行建模

Question

给定的位置（城市）可以有一个名称以及其他可以识别该位置的别名。我需要在数据库中对此进行建模。

可以使用城市或其任何别名执行搜索：

例如

城市：

• 名称：洛杉矶
• 别名：LA

指定搜索条件时，我可以指定 LA 或洛杉矶，它应该返回相同的结果（例如好莱坞） 。

我正在考虑使用一对多关系来做到这一点，其中 1 个城市可以有多个别名，并且许多别名可以映射到一个城市。

在进行搜索时，我使用城市和城市别名表的联接来查找正确的城市。

有更好的方法来处理这个问题吗？

编辑： （对于遇到相同问题/要求并碰巧来到此页面的任何人） 请参阅我的答案，因为我最终使用了它，但标记的答案可以帮助您识别独特的城市。

Answer 1

我可以添加到您的解决方案中的唯一一件事是，您可以尝试首先在城市表中查找完全匹配的内容，如果没有，则加入别名。这样您就可以跳过一些非常昂贵的连接。

另一件需要注意的事情是，这个双表解决方案可能会遇到重复条目的问题。我不是在谈论不同城市的相同别名（这可以使用唯一列进行检查），而是与城市名称匹配的别名。这些“重复条目”的示例，后跟详细说明：

城市

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

别名

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

我知道这不应该发生......但你知道摩尔定律:)这些跨表重复可能会给您在查找表中带来麻烦（我想您正在使用它们作为查找来“猜测”当他/她写下“LA”时，城市实际上试图选择什么用户）。但如果用户写了“洛杉矶”，您就必须决定是优先考虑城市还是别名。我知道我提供的例子有点愚蠢，但作为非美国公民，我无法提供更好的例子。但是有很多城市，每个城市都有很多别名...我不会冒险:)

首先检查城市表将使该城市优先于其他城市的同等命名别名。或者，您可以在插入之前检查城市别名是否已作为城市名称存在。

这就是我能想到的:)

Answer 2

一些注意事项：

DestinationAlias 表不需要代理键。 (idDestination, alias)（或相反）可以用作PRIMARY KEY。

要消除两个表中的（公共）名称重复以及可能出现的问题，您可以从 Destination 表中删除 name 列并添加 DestinationDefaultAlias 表，与 DestinationAlias 具有 1:1 关系（以及与 Destination< 隐含的 1:1 关系） /代码>）：

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

当您想要查找默认名称时，请将 Destination 与 DestinationDefaultAlias 连接起来。要搜索所有别名，请使用 DestinationAlias 加入。

Answer 3

假设：

• La Aguardia 是一个随机城市名称
• LA 是 La Aguardia 的别名

• 洛杉矶是纽约的别名

• 对于添加的每个城市，添加城市名称本身作为别名表的条目，因此我们只需搜索别名表。

城市表：

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

别名表：

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

用例 1：

搜索 LA：产量 (cityID) => [1, 3] =独特> [洛杉矶，La Aguardia]

用例 2：

搜索洛杉矶：产量 (cityID) => [1, 2] =独特> [洛杉矶、纽约]

用例3：

搜索纽约：yields (cityID) => [2,2]=独特> [纽约]