使用用户输入进行数据库规范化

Question

我开发了一个 mysql 数据库，其中包含每个用户的国家、城市和职业。 虽然我可以使用“国家/地区”表，然后将国家/地区的 ID 插入到用户表中，但我仍然必须为其他两个表寻找完美的方法。

问题在于，每个用户的城市和职业都是从输入字段中获取的，这意味着用户可以为每个城镇键入“NYC”或“New York”或“New York City”以及数百万个其他组合。

忽略这个问题，创建一个自己的“town”表，其中包含用户插入的所有城镇，然后将城镇条目的 id 放入用户表中，或者使用 VARCHAR 列“town”是否更合适？ “在用户表中并且没有规范化有关此关系的数据库？ 我想在用户个人资料页面上显示三个表中的数据。

我担心规范化，因为我不想在数据库中有太多冗余数据，因为它会消耗大量空间，并且如果我使用 varchar 索引而不是整数索引，查询会变慢（例如我知道）：

谢谢

Answer 1

我们遇到了这个问题。我们的解决方案是收集人们使用的各种同义词和包含拼写错误的版本，并将它们明确映射到已知的规范城市名称。这允许在 99% 的情况下根据用户输入正确猜测名称。

对于剩下的 1%，我们创建了一个新的城市条目并将其标记为非规范。我们定期查看非规范条目。对于可识别的已知城市，我们将非规范条目重新映射到规范条目（更新链接记录的 FK 并添加同义词）。对于我们不知道的真正的新城市名称，我们将创建的条目保留为规范。

所以我们有这样的事情：

 table city(
   id integer primary key,
   name varchar not null, -- the canonical name
   ...
 );

 table city_synonym(
   name varchar primary key, -- we want unique index
   city_id integer foreign key references(city.id)
 );

Answer 2

通常，数据标准化可以帮助您处理数据并使其保持简单。如果规范化模式不满足您的需求，您也可以使用非规范化数据。所以这取决于您想要使用的查询。

如果不创建单独的表（将每个城市的所有名称保存在单个 ID 中），就没有好的解决方案来对城市进行分组。因此，最好有 3 个表：user(user_id, city_id)、city(city_id, 正确名称)、city_alias(alias_id, city_id, name)。

Answer 3

最好以标准化设计存储数据，其中包含实际、政府认可城市名称。

@Varela 关于城市“别名”的建议在这种情况下可能会很有效。但您必须返回一条类似“您输入了‘Now Yerk’。您的意思是‘纽约’吗？”的消息。实际上，无论如何，您都希望获得这些类型的更正...

---

当然，您可能实际上存储的不是城市，而是邮政编码。表设计遵循以下原则：

State:
Id   State
============
AL   Alabama
NY   New York

City:
Id   State_Id   City
========================
1    NY         New York 
2    NY         Buffalo

Zip_Code:
Id  Code         City_Id
=========================
1   00001-0001   1

然后，只要您有地址，就存储对 Zip_Code.Id 的引用。您想确切地知道用户拥有（声称）属于哪个邮政编码。原因包括：

1. 零售税（无论亚马逊的表现如何）。
2. 送货地址（例如，华盛顿和纽约都有贝尔维尤。邮政编码不同）。
3. 社会地图。如果将其存储为“用户输入”城市，您将无法（轻松）分析数据以找出诸如哪些用户居住在彼此附近的信息，更不用说在同一城市了。

关于地址验证，还可以执行许多其他操作，包括地理位置，但这是一个基本设计，可以帮助您满足大多数需求（并防止大多数可能的“无效”异常）。