database-design - 如何为具有许多 m 的数据库建模 :n relations on a table

Question

我目前正在建立一个具有大量多对多关系的数据库。每个关系都是通过链接表建模的。例子:

一个人有多个工作，工作由多个人完成。一个人有好几间房子，房子被好几个人住着。一个人有许多他喜欢的餐馆，餐馆有许多喜欢这家餐馆的人。

首先我是这样设计的:

表:Person、Job、House、Restaurant、Person_Job、Person_House、Person_Restaurant。

关系 1 - n:Person -> Person_Job，Person -> Person_House，Person -> Person_Restaurant，Job -> Person_Job，House -> Person_House，Restaurant -> Person_Restaurant。

这很快就会导致拥挤和复杂的 ER 模型。

为了简化这一点，我将其建模如下:

标签:人、工作、房子、餐厅、Person_Attributes

关系 1 - n:Person -> Person_Attributes，Job -> Person_Attributes，House -> Person_Attributes，Restaurant -> Person_Attributes

Person_Attributes 表应如下所示:
人名
作业编号
户号
餐厅编号

如果一个人 - 工作关系存在，我将添加一个条目，如下所示:

P1, J1, NULL, NULL

如果一个人 - 房子关系存在，我将添加一个条目，如下所示:

P1，空，H1，空

因此，第二个示例中的属性表将具有与第一个示例的链接表相加的相同数量的条目。

这大大简化了 ER 模型，只要我为 personId + jobId、personId + houseId 和 personId + restaurantId 构建索引，我认为不会有太多性能影响。

我的问题是:
第二种方法是建模的正确方法吗？如果不是，为什么？
我对性能影响是否正确？如果不是，为什么？

我的意思的 MySQL Workbench 示例可以在这里找到:

http://www.2shared.com/file/3GBnodEZ/example.html

Answer 1

您的设计违反了 Fourth Normal Form .您试图在一张表中存储多个“事实”，这会导致异常。

The Person_Attributes table should look something like this: personId jobId houseId restaurantId

因此，如果我与一份工作、一所房子、两家餐厅相关联，我是否应该存储以下内容？

personId jobId houseId restaurantId
    1234    42      87         5678
    1234    42      87         9876

如果我添加第三家餐厅，我会复制其他列吗？

personId jobId houseId restaurantId
    1234   123      87         5678
    1234   123      87         9876
    1234    42      87        13579 

完毕!哦，等等，那里发生了什么？我在增加新餐厅的同时换了工作。现在我错误地与两个工作相关联，但无法区分这与正确与两个工作相关联。

另外，即使与两个工作相关联是正确的，数据不应该是这样的吗？

personId jobId houseId restaurantId
    1234   123      87         5678
    1234   123      87         9876
    1234   123      87        13579 
    1234    42      87         5678
    1234    42      87         9876
    1234    42      87        13579 

它开始看起来像 Cartesian product jobId、houseId 和 restaurantId 的所有不同值。事实上，它是——因为这个表试图存储多个独立的事实。

正确的关系设计需要为每个多对多关系建立一个单独的交集表。抱歉，您还没有找到快捷方式。

(许多关于归一化的文章都说超过 3NF 的更高范式是深奥的，人们永远不必担心 4NF 或 5NF。让这个例子反驳这一说法。)

关于使用 NULL 的评论:那么您在强制唯一性时遇到问题，因为 PRIMARY KEY约束要求所有列都不是 NULL。

personId jobId houseId restaurantId
    1234   123      87         5678
    1234  NULL    NULL         9876
    1234  NULL    NULL        13579 

另外，如果我在上表中添加第二个房子或第二个jobId，我应该把它放在哪一行？你可能会得到这样的结果:

personId jobId houseId restaurantId
    1234   123      87         5678
    1234  NULL    NULL         9876
    1234    42    NULL        13579 

现在，如果我解除 restaurantId 9876 的关联，我可以将其更新为 NULL。但这留下了一行所有的 NULL，我真的应该删除它。

personId jobId houseId restaurantId
    1234   123      87         5678
    1234  NULL    NULL         NULL
    1234    42    NULL        13579 

而如果我取消了 13579 餐厅的关联，我可以将其更新为 NULL 并将该行保留在原位。

personId jobId houseId restaurantId
    1234   123      87         5678
    1234  NULL    NULL         9876
    1234    42    NULL         NULL 

但是，如果该列中有空缺，我不应该合并行，将 jobId 移到另一行吗？

personId jobId houseId restaurantId
    1234   123      87         5678
    1234    42    NULL         9876

问题是，现在添加或删除关联变得越来越复杂，需要多个 SQL 语句进行更改。您将不得不编写大量乏味的应用程序代码来处理这种复杂性。

但是，如果您 ，所有各种更改都很容易。每个多对多关系定义一个表。 您确实需要拥有更多表的复杂性，但通过这样做，您将简化您的应用程序代码。

将关联添加到餐厅只是 INSERT到 Person_Restaurant 表。删除该关联只是一个 DELETE .与工作或房屋有多少关联并不重要。并且您可以在每个交集表中定义主键约束以强制唯一性。