SQL 成员表显示成员之间的家庭关系

Question

我几天来一直在尝试设计这个数据库。我在 access 中设计它，然后在 MYSQL Server 上实现为 PHP Web 应用程序。

我有一张表：

Members

• MemberID (autoNumber, PK)
• MemberName
• MemberDetails

成员可以与其他成员有很多关系，这可能是孩子、父母、朋友、配偶等。查看一个成员，我会希望能够与现有成员创建新的关系，然后让相关成员也可以看到该关系，而无需进一步输入。成员还应该能够列出他们所有的关系。

你能告诉我应该怎么做吗？我尝试了一些选项，但似乎没有一个能按预期工作。我对 SQL 很满意，只是在一元关系设计方面遇到了麻烦。

-编辑- 另外，我忘了补充一下，由于服务器限制，这将无法使用 INNODB。最有可能的是 MYISAM，尽管我仍然想要引用完整性:(

Answer 1

让表members 包含成员数据，表relations 包含成员关系数据。
relations.member_id 将是对成员的引用，relations.lated_member_id - 相关成员的引用。
relations.type 是可枚举的关系类型。

CREATE TABLE `members` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` TEXT NOT NULL ,
`details` TEXT NOT NULL
) ENGINE = INNODB;

CREATE TABLE `relations` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`member_id` INT NOT NULL ,
`related_member_id` INT NOT NULL ,
`type` ENUM(  'Child',  'Parent',  'Friend',  'Spouse' ) NOT NULL,
FOREIGN KEY (member_id) REFERENCES members(id),
FOREIGN KEY (related_member_id) REFERENCES members(id)
) ENGINE = INNODB;

UPD： MyISAM 版本（删除了外键，所有可能的外键功能应由服务器端脚本处理）：

CREATE TABLE `members` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` TEXT NOT NULL ,
`details` TEXT NOT NULL
) ENGINE = MyISAM;

CREATE TABLE `relations` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`member_id` INT NOT NULL ,
`related_member_id` INT NOT NULL ,
`type` ENUM(  'Child',  'Parent',  'Friend',  'Spouse' ) NOT NULL
) ENGINE = MyISAM;

Answer 2

从@Minras设计（+1）开始，我

MEMBERS
  MemberId
  Name
  Details

RELATIONS
  FromMemberId
  ToMemberId
  RelationType

会添加第三个表，而不是检查约束：

RELATIONTYPE
  RelationType
  Description
  FromLabel
  ToLabel

RelationType是整数，“标签”是字符数据。该关系是“定向”的，因为您必须密切注意哪个成员是“来自”，哪个是“到”（但对于“非定向”关系并不那么重要，例如“上过高中”）一起”）。这种设计将允许您：

• 定义具有多个标签的人之间的关系，例如，A 是 B 的父亲，B 是 A 的儿子。
• 如果、何时和必要时添加新的关系

显然，您要么通过外键或任何可用的东西在所有事物上保持关系完整性，要么您将等待发生火车失事。

这并没有解决如何唯一、清晰地识别重名成员的问题。为此，您要么需要考虑现实世界中人们用来处理此类情况的识别属性（学生 ID？社会安全号码？），要么引入特定于您的应用程序的工件（例如，登录名 + 密码） ）。

Answer 3

尝试添加数据透视表：

Relationships:

MemberId1
MemberId2
RelationshipType

Answer 4

创建一个带有列的表：

Member_1_id
Member_2_id
Relation_type

然后您可以像这样使用它：
如果爱丽丝是鲍勃的女儿，您将拥有这些关系：

<Bob id> <Alice id> 'Father'
<Alice id> <Bob id> 'Daughter'

然后您可以在这些关系中添加一些额外的数据，例如关系何时开始（即某人何时订婚）。

您可能还想在具有成员 ID 的两列上创建索引。

编辑：
为了避免在该表中重复数据，您可以创建一个视图，然后将每个关系仅存储为一行。然而，这个解决方案不允许您命名诸如“父亲”和“儿子”之类的关系。

SELECT member1, member2, relation FROM rel_table UNION ALL SELECT member2, member1, relation FROM rel_table;