在单个表的多行之间建立关系的正确方法

Question

我有以下情况：我想用某种关系连接一个表中的多条记录。记录可以与其他记录没有连接，或者可以有多个记录(1 个或更多)。这种关系没有等级。

例如：

CREATE TABLE x
(
x_id SERIAL NOT NULL PRIMARY KEY,
data VARCHAR(10) NOT NULL
);

我想到了两个想法：

1. 在此表中创建一个新列，该列将包含一些关系 Id。它不会引用任何内容。插入新记录时，我将生成新的关系 ID 并将其放在那里。如果我想将其他记录与此记录连接，我将简单地放置相同的关系 Id。

例：

CREATE TABLE x
(
x_id NUMBER(19, 0) NOT NULL PRIMARY KEY,
data VARCHAR(10) NOT NULL,
relation_id NUMBER(19, 0) NOT NULL
);
insert into x values (nextval, 'blah', 1);
insert into x values (nextval, 'blah2', 1);

它将连接这两行。

优点：

• 非常容易
• 轻松查询，将所有记录连接到特定记录
• 无开销

缺点：

• 休眠实体将仅包含关系 ID，不包含 相关记录(或者也许以某种方式可能？

  2. 创建一个单独的连接表，并连接具有多对多关系的行。连接表将包含两列带 id 的列，因此一个条目将连接两行。

例：

CREATE TABLE x
(
x_id SERIAL NOT NULL PRIMARY KEY,
data VARCHAR(10) NOT NULL
);
CREATE TABLE bridge_x
(
x_id1 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
x_id2 NUMBER(19, 0) NOT NULL REFERENCES x (x_id),
PRIMARY KEY(x_id1, x_id2)
);
insert into x values (1, 'blah');
insert into x values (2, 'blah2');
insert into bridge_x values (1, 2);
insert into bridge_x values (2, 1);

优点：

• 规范化关系
• 轻松休眠实体映射，集合包含相关 记录

缺点：

• 开销(有多个连接的行，必须插入每对)

最好的方法是什么？除了这两个，还有别的办法吗？

Answer 1

根据我的经验，最好的方法是按照您在第二个选项中所说的那样使用规范化。您在这里要查找的是创建外键。

因此，如果您使用示例 2 中给出的示例，然后应用以下 SQL 语句，您将创建一个可以具有 0 到多个关系的关系数据库。

ALTER TABLE `bridgex` ADD  CONSTRAINT `fk_1` FOREIGN KEY (`xID`) REFERENCES `x`(`xID`) ON DELETE NO ACTION ON UPDATE NO ACTION;