php - MySQL 数据库 : How far to Normalize/Queries VS Join/Unique Index

Question

最近我发现自己在设计一个数据库。数据库由几个表(InnoDB)组成:

表 1:国家/地区(id，country_name)

表 2:城市(id、city_name、countryid)

表 3:用户(id、cityid、A、B、C、D、E)

在Users表中，A、B、C、D和E是用户的一些特征，其中特征 A 如果将它与 cityid 结合使用，则它必须是唯一的，这就是我为这两列创建唯一索引的原因:

CREATE UNIQUE INDEX idx_user ON Users(cityid , A);

其余列B、C、D和E是其他用户特征(例如头发颜色、高度、体重等)，如您所知，将在表格中重复(头发颜色 = 黑色，或体重 = 75 公斤)。

同时 countryid 和 cityid 在 UPDATE 和 DELETE CASCADE 上配置为外键。

搜索将基于 cityid 和 A 列。用于选择城市的下拉菜单(因此 cityid)和用于插入特征 A 的文本框，然后点击“搜索”按钮。

我的问题是:

1. 在 Users 表中，我在同一列(列 B、C、D 和 E)中有重复数据。这违反了2NF。我是否必须为这些列中的每一列创建一个单独的表，然后将这些表中的每一个的外键分配给 Users 表以实现 2NF？

   表B(id, Bchar)

   C表(id，Cchar)

   表D(id, Dchar)

   表E(id, Echar)

   用户(id、cityid、A、Bid、Cid、Did、Eid)

2. 目前我不会使用 B,C,D 和 E 列作为搜索数据，仅在使用 cityid< 搜索后显示它们/strong> 和 A 搜索。如果(将来)我决定我需要显示居住在 cityid 并且有黑头发 的用户 的所有结果，我该怎么做现在在设计数据库时必须牢记什么？

3. 我们一方面有 DML(插入、更新、删除)，另一方面有查询 (SELECT)。 DML 将在规范化数据库和非规范化数据库上更快地工作。有中间解决方案吗？

4. 上面创建的 UNIQUE INDEX 是否足以确保 cityid 和 A 列中数据组合的唯一性？我是否需要使用 JavaScript 或更好的 PHP 进一步限制它？

5. 多查询 VS 联接:规范化数据库将需要多个查询或一个带有连接的查询。在“用户搜索来自马德里的具有特征A的用户”的情况下:

   a) 多个查询:

   i) 转到城市表并找到马德里的id(例如，id = 2)

   ii) 给定马德里 ID 和特征 A 的输入，转到Users 表并SELECT * FROM Users WHERE cityid="2"AND A="characteristic";

   b) 内部连接:

   i) SELECT City.city_name, Users.B, Users.C FROM City INNER JOIN Users ON Users.cityid = City.id;
   

   我应该选择哪一个？

提前致谢。

Answer 1

你的表已经在 2NF 中了。2NF 的条件是不应该有部分依赖。例如，让你的 users 表和 user-id 是主要的key 和另一个更适合调用 candidate key 的主键是 (cityid,A) ，您可以用它唯一地表示表中的一行。如果您的表不在 2NF 中仅 cityid 或 A 就足以唯一地检索 B、C、D 或 E，但在您的情况下，需要同时 (cityid,A) 来检索唯一记录，因此它已经标准化。

注意:

你的表不在 3NF 中。3NF 的条件是没有传递依赖。让我们在这里使用 users 表，userid 是主键，你可以得到一个唯一的 (cityid,A) 对，然后你可以得到从 userid 获得的具有 (cityid,A) 的唯一 (B,C,D,E) 记录。简而言之，如果 A->B 和 B->C 间接 A->C，这称为传递依赖，它存在于您的用户中表，因此它不是 3NF 的合适候选者。