代码之家 › 专栏 › 技术社区 › Duncan Lock

有一张我想互相联系的人的表,很多人对很多人,链接是双向的

data-modeling many-to-many

Duncan Lock · 技术社区 · 14 年前

假设您生活在一个非常简单的示例环境中—假设您的MySQL数据库中有一个人员表:

create table person (
    person_id int,
    name text
)

select * from person;

+-------------------------------+
|   person_id |            name |
+-------------------------------+
|           1 |           Alice |
|           2 |             Bob |
|           3 |           Carol |
+-------------------------------+

这些人需要合作,所以你有一个链接表,将一个人的记录链接到另一个人的记录:

create table person__person (
    person__person_id int,
    person_id int,
    other_person_id int
)

这种设置意味着人与人之间的链接是单向的,即爱丽丝可以链接到鲍勃,而鲍勃不可以链接到爱丽丝,更糟的是,爱丽丝可以链接到鲍勃。和 Bob可以在两个单独的链接记录中同时链接到Alice。由于这些链接代表工作关系,在现实世界中它们都是双向的相互关系。此设置中可能会出现以下所有情况:

select * from person__person;

+---------------------+-----------+--------------------+
|   person__person_id | person_id |    other_person_id |
+---------------------+-----------+--------------------+
|                   1 |         1 |                  2 |
|                   2 |         2 |                  1 |
|                   3 |         2 |                  2 |
|                   4 |         3 |                  1 |
+---------------------+-----------+--------------------+

例如,对于上面的person_u person_u id=4,当您查看Carol的(person_u id=3)配置文件时,您应该看到与Alice的关系(person_u id=1),当您查看Alice的配置文件时,您应该看到与Carol的关系,即使链接是相反的。

我意识到我可以进行联合和不同的查询,以及在UI中不将关系表示为相互关系的什么,但是有更好的方法吗?我有种感觉是一个更好的方法是,通过正确地设置数据库,这个问题会完全消失,但我看不到。有人有更好的主意吗?

4 回复 | 直到 14 年前

shad 14 年前

我不确定是否有更好的方法来配置您的表。我认为你拥有它们的方式是恰当的,并且我会实施它。

因为你的关系表可以表示单向关系,我建议这样对待它们。换句话说,对于每个关系,我将添加两行。如果Alice与Bob合作,表格应如下:

select * from person__person;
+---------------------+-----------+--------------------+
|   person__person_id | person_id |    other_person_id |
+---------------------+-----------+--------------------+
|                   1 |         1 |                  2 |
|                   2 |         2 |                  1 |
+---------------------+-----------+--------------------+

原因是,在许多类似ActiveRecord(Rails)的系统中,多对多表对象不够智能,无法同时查询Person_ID和其他_Person_ID。通过保留两行,ActiveRecord类对象将正常工作。

您应该做的是在代码级别加强数据的完整性。每次在两个用户之间建立关系时,都应该插入两个记录。当关系被破坏时,两个记录都应该被删除。不应允许用户与其建立关系。

lexu 14 年前

我看不到使用简单的关系概念。您必须添加“业务”代码来强制您的人际关系。

一种方法是使用insert触发器强制执行第二个关系记录
然后声明其中一个记录“主要”(例如,个人ID小于另一个人ID的记录)
然后为使用这些知识访问数据的应用程序(选择、更新、删除)构建视图和粘合代码。

lexu 14 年前

没有更好的主意。关系数据库不能强制执行您的请求,因此您必须编写一个特殊的查询来检索数据,并编写一个触发器来强制执行约束。

为了获得@person的相关人员,我会选择:

SELECT CASE person_id WHEN @person 
           THEN other_person_id 
          ELSE person_id
       END as related_person_id  
FROM person_person 
WHERE (  person_id=@person 
      OR other_person_id=@person)

Rob 14 年前

你会发现这篇文章很有用:

http://discuss.joelonsoftware.com/default.asp?design.4.361252.31

正如@user所发布的那样,您通常最好为每个双向关系创建两个记录(在示例中为alice to bob和bob to alice)。它使查询变得多更简单,更准确地反映了关系。如果你确实有真正的单向关系,这是唯一的飞行方式。