数据库设计模式：跨表的多对多关系？

I have the following tables:

我有以下表格:

Section and Content

部分和内容

And I want to relate them.

我想把它们联系起来。

My current approach is the following table:

我目前的方法是下表:

In which I would store

我会在哪里存放

• Section to Section

部分到部分

• Section to Content

部分内容

• Content to Section

内容到部分

• Content to Content

内容到内容

Now, while I clearly can do that by adding a pair of fields that indicate whether the source is a section or a content, and whether the target is a section or a content, I'd like to know if there's a cleaner way to do this. and if possible using just one table for the relationship, which would be the cleanest in my opinion. I'd also like the table to be somehow related to the Section and Content tables so I can avoid manually adding constraints, or triggers that delete the relationships when a Section or Content is deleted...

现在,虽然我明显可以通过添加一对字段来指明源是节还是内容,以及目标是节还是内容,但我想知道是否有更简洁的方法这个。如果可能的话,只使用一张表来表示关系,这在我看来是最干净的。我还希望该表以某种方式与Section和Content表相关,这样我就可以避免手动添加约束,或者在删除Section或Content时删除关系的触发器...

Thanks as usual for the input! <3

像往常一样感谢输入! <3

2 个解决方案

#1

Here's how I would design it:

这是我如何设计它:

CREATE TABLE Pairables (
  PairableID INT IDENTITY PRIMARY KEY,
  ...other columns common to both Section and Content...
);

CREATE TABLE Sections (
  SectionID INT PRIMARY KEY,
  ...other columns specific to sections...
  FOREIGN KEY (SectionID) REFERENCES Pairables(PairableID)
);

CREATE TABLE Contents (
  ContentID INT PRIMARY KEY,
  ...other columns specific to contents...
  FOREIGN KEY (ContentID) REFERENCES Pairables(PairableID)
);

CREATE TABLE Pairs (
  PairID     INT NOT NULL,
  PairableId INT NOT NULL,
  IsSource   BIT NOT NULL,
  PRIMARY KEY (PairID, PairableID),
  FOREIGN KEY (PairableID) REFERENCES Pairables(PairableID)
);

You would insert two rows in Pairs for each pair.

您将为每对在对中插入两行。

Now it's easy to search for either type of pairable entity, you can search for either source or target in the same column, and you still only need one many-to-many intersection table.

现在很容易搜索任何类型的可配对实体,您可以在同一列中搜索源或目标,并且您仍然只需要一个多对多交叉表。

#2

Yes, there is a much cleaner way to do this:

是的,有一个更清洁的方法来做到这一点:

• one table tracks the relations from Section to Section and enforces them as foreign key constraints

一个表跟踪从一个部分到另一个部分的关系,并将它们强制为外键约束

• one table tracks the relations from Section to Content and enforces them as foreign key constraints

一个表跟踪从Section到Content的关系,并将它们强制为外键约束

• one table tracks the relations from Content to Section and enforces them as foreign key constraints

一个表跟踪从Content到Section的关系,并将它们强制为外键约束

• one table tracks the relations from Content to Content and enforces them as foreign key constraints

一个表跟踪从内容到内容的关系,并将它们强制为外键约束

This is much cleaner than a single table with overloaded IDs that cannot be enforced by foreign key constraints. The fact that the data modeling, nor the domain modeling patterns, never mention a pattern like the one you describe should be your first alarm bell. The second alarm should be that the engine cannot enforce the constraints you envision and you have to dwell into triggers.

这比具有重载ID的单个表更加清晰,无法通过外键约束强制执行。数据建模和域建模模式从未提及类似于您描述的模式的事实应该是您的第一个警钟。第二个警报应该是引擎无法强制执行您想象的约束并且您必须停留在触发器中。

Having four distinct relationships modeled in one table brings no elegance to the model, it only adds obfuscation. Relational model is not C++: it has no inheritance, it has no polymorphism, it has no overloading. Trying to enforce a OO mind set into data modeling has led many a fine developers into a mud of unmaintainable trigger mesh of on-disk table-like bits vaguely resembling 'data'.

在一个表中建立四个不同的关系不会给模型带来任何优雅,它只会增加混淆。关系模型不是C ++:它没有继承,它没有多态,它没有重载。试图在数据建模中强制执行OO思维导致许多优秀的开发人员陷入了无法维护的磁盘表状位触发网格,模糊地类似于“数据”。