用于分组记录的数据库模式模式 [英] Database schema pattern for grouping records

问题描述

我正在寻找一种设计模式来管理关系数据库中相互关联的记录，例如分组多个联系人。小组本身不必是实体。组中的成员数应无限制。

I'm looking for a design pattern to manage records in a relational database which belong together, e.g. grouping multiple contacts. The group itself does not need to be an entity. A group should have an unlimited count of members.

应该可以运行查询以获取给定记录的其他组成员。

It should be possible to run a query to get other group members of a given record.

我的想法是在一个表中对其进行管理：

My idea is to manage it in one table:

GROUPINGS
  integer group
  integer member_id

primary_key (group, member_id)
foreign_key (member_id)

编辑：请注意，组不是不是外键。它只是一个唯一的标识符。

Beware that group is not a foreign key. It's just a unique identifier. It should be increased for every member group which is built.

这里是一个示例内容：

GROUPINGS group | member_id
          -----------------
              1 | 10
              1 | 11
              1 | 12
              2 | 20
              2 | 21
              3 | 10
              3 | 40

此示例包含三组：（10,11,12）和（20,21）和（10,40）。您会看到10个包含在两个组中。

This example contains three groups: (10,11,12) and (20,21) and (10,40). You see that 10 is included in two groups.

要查询成员10的邻居，我们可以使用以下SQL语句：

To query the "neighbors" of member 10 we can use this SQL statement:

SELECT g2.member_id
FROM groupings g1
JOIN groupings g2 ON g1.group      = g2.group 
                 AND g1.member_id != g2.member_id
WHERE g1.member_id = 10

=> 11,12,40

您怎么看？

编辑：将表组重命名为分组，然后将属性 group_id重命名为 group以使该表中的记录显然不是组-这是组与成员之间的链接。组不是实体。

Renamed table "groups" to "groupings" and renamed attribute "group_id" to "group" to make it obvious that a record in this table is not a group - it's a link between a group and a member. Group is not an entity.

推荐答案

这篇关于用于分组记录的数据库模式模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！