database design pattern: many to many relationship across tables?

Question

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...

Thanks as usual for the input! <3

Answer 1

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
• one table tracks the relations from Content to Section and enforces them as foreign key constraints
• 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.

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'.

Answer 2

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.