在PostgreSQL中，存储一对多或多对多关系的最佳方法是什么？

Question

我目前正在将一个开源聊天工具（AJAX Chat）集成到另一个项目中。现在，该聊天工具默认从一个文件中获取有效的用户和频道，但是当你有一个不断变化的用户数据库时，这显然不是理想的选择。

所以，我想让聊天工具直接从数据库加载用户和频道信息。我的想法是这样设计的（如果你有不同的看法，请告诉我）：

- 我们有一些聊天频道（公共频道、营销频道等等） - 然后我们把组分配给频道（比如PR团队1、IT人员等等） - 然后我们有用户，他们是组的一部分，并且在某些情况下直接分配给了频道。

我打算用以下这些表来实现上述设计：

频道表：

|----|Channel_Name||Channel_ID||Groups_Assigned||Users_Assigned|----|  
|----|---Public---||-----0----||---1,2,3,4,5---||-----3,4------|----|  
.  
.  
.etc...

注意：分配的组表包含分配给频道的组ID，而分配的用户包含不属于分配组的用户的ID。

组表：

|----|Group_Name||Group_ID||Users_Assigned|----|  
|----|---Team1--||----0---||------5,10----|----|  
.  
.  
.etc...  

很抱歉表格画得不太好。

现在，通过以上的实现，当用户登录时，程序会获取用户ID（从用户表中），然后搜索包含该用户ID的所有组，并最终搜索包含任一组（用户是其中一员）或直接分配给用户的所有频道的通道表。

我的想法是可行的，但似乎有点低效。因为我必须以1,2,3....的格式存储分配的ID（组和用户），所以我必须使用PHP的explode()或其他一些可以搜索字符串的PostgreSQL函数。 我可能会存储一组组的数组，然后逐个循环遍历它们，这对我来说似乎非常慢。

或者，我可以为每个用户设置一个布尔列，但这将导致列过多，而且我不想在创建用户时每次都创建新的列。

那么，你们会怎么做呢？如果有人出于某种疯狂的原因同意我的初始想法，那能否帮我弄清楚如何编写代码来实际执行它。

感谢您的时间，祝您有愉快的一天。

- zermy

---

你的直觉是正确的，这不是一个好的设计。阅读此 is-storing-a-comma-separated-list-in-a-database-column-really-that-bad：https://dev59.com/gHA65IYBdhLWcg3wvxaE - ypercubeᵀᴹ

Answer 1

是的，将逗号分隔的数字字符串存储起来并尝试在数据库中查找给定数字是低效的。有关更多信息，请参见我的回答在数据库列中存储逗号分隔列表真的很糟糕吗？ 相反，您应该使用“交集表”来存储用户和组之间以及组和频道之间的多对多关系。然后，您的搜索将受益于索引，并且可以使用连接返回到组或频道表。

- Bill Karwin

Answer 2

我建议使用一个表格代替 1,2,3,4,5 的值，因为它们很难阅读。将 Groups_Assigned 从 channels 表中移除，并将其放入单独的表格中，采用一对多的格式：

Channel_id  Group_id
----------  --------
0           1
0           2
0           3
0           4
0           5

我会创建另一个表格作为组，将其加入到此处的表格中，并保存有关每个group_id的信息。然后，编写能够按需读取此设计的查询即可。

- Twelfth

---

我以groups_assigned为例，同样的逻辑也适用于user_assigned。可以将频道和组“user_assigned”都指向一个单独的“user_assigned”表。 - Twelfth

谢谢，我可能最终会做类似这样的事情。我曾经想过这个解决方案，但由于某种原因，我的蠢脑子将其驳回了。 - zermy

嘿，不傻...曾经我也会这样做。我相信我从像这样的论坛中学到了不同的方法，呵呵，循环继续。如果你从线性的东西转向数据库设计...请记住，在SQL中数组很糟糕。在这种形式下，一对多关系更容易阅读，无论是从代码的易用性还是性能的角度来看。 - Twelfth

Answer 3

一种可能的解决方案：

Channel
------------
Channel_Id
Channel_Name
PRIMARY KEY (Channel_Id)

Person 和 Grouping（我更喜欢这些，而不是 User 和 Group，因为有些系统将其用作关键字）可以视为超类型 Entity 的子类型。这将有助于后续只有一个 Assignment 表。

Entity
------------
Entity_Id
PRIMARY KEY (Entity_Id)

Person  --- ( User )
------------
Person_Id
Person_Name
--- other data about persons/users
PRIMARY KEY (Person_Id)
FOREIGN KEY (Person_Id)
  REFERENCES Entity(Entity_Id)

Grouping   --- ( Group )
------------
Grouping_Id
Grouping_Name
--- other data about groups
PRIMARY KEY (Grouping_Id)
FOREIGN KEY (Grouping_Id)
  REFERENCES Entity(Entity_Id)

这将用于人员 - 分组关联：

Belongs --- ( Person Belongs In Grouping )
------------
Person_Id
Grouping_Id
PRIMARY KEY (Person_Id, Grouping_Id)
FOREIGN KEY (Person_Id)
  REFERENCES Person(Person_Id)
FOREIGN KEY (Grouping_Id)
  REFERENCES Grouping(Grouping_Id)

为了和频道的任务关联起来，需要一个关联表。

Assignment ( Entity is Assigned to Channel )
------------
Entity_Id
Channel_Id
PRIMARY KEY (Entity_Id, Channel_Id)
FOREIGN KEY (Entity_Id)
  REFERENCES Entity(Entity_Id)
FOREIGN KEY (Channel_Id)
  REFERENCES Channel(Channel_Id)

---

当然，您可以摆脱“Entity”表，并拥有两个关联表，一个用于“人员到频道”的分配，另一个用于“组到频道”的分配。

- ypercubeᵀᴹ

---

哦，这是一个不错的解决方案，但我已经有了一个填充的用户表，所以我觉得我必须问一下：“当我已经有一个用户表和超过300个其他表时，添加外键对我来说有多安全（我知道我必须先创建实体，然后引用它们）。”除此之外，我同意你和其他人的答案，我应该使用交叉表。 - zermy

如果您有许多表引用了用户表，那么无需更改这些引用。如果您已经拥有了用户和组表，并且想要添加一个实体表，那将会很棘手（在这两个表中的一个中，主键值必须更改，因此要么更改 Users.User_Id 或者 Groups.Group_Id 以及所有对这些值的引用）。 - ypercubeᵀᴹ

此外，您需要调整将存储过程 INSERT 到用户和组的方式（首先插入实体，然后使用新的主键并将其插入到用户或组）。 - ypercubeᵀᴹ

在这种情况下，您应该使用2个交集表，一个用于“用户-频道”关联，另一个用于“组-频道”。 - ypercubeᵀᴹ

@Catcall：是的，你说得对，通过添加唯一键而不干扰现有的主键，可以实现相同的引用完整性。我没有想到这一点。虽然这不是最好的解决方案，但当已经存在数据时，我想这可能是唯一需要最少更改表格且不需要在现有（PK）数据中进行任何更改的解决方案。 - ypercubeᵀᴹ