如何解决三向多态关联? [英] How can I resolve a three-way polymorphic association?

问题描述

首先让我说我正在使用MySQL(不是事务性的)，并且不能更改.另外，为简洁起见，我在这里简化了表格.

First let me say I am using MySQL (not transactional) and that this can't be changed. Also, I have simplified the tables here for brevity and clarity.

在此示例中，课程"由其内部属性和外部属性以及其自己的属性读数"组成. 读数"具有自己的键相关属性和三个不同的外部属性(阅读源).

In this example a 'Lesson' is comprised of it's internal attributes and an external attributes with it's own attributes 'Readings'. 'Readings' has it's own key dependent attributes and three distinct external attributes (the reading sources).

我想避免出现在这里的多态关联，但是无法将我的头缠住它.在此示例中，表"Reading"中的"sourceId"将包含三个表"ExternalURL"，"InternalURL"和"Book"之一的ID.另外，"polytable"字段将包含上述"id"来自的表名.

I want to avoid the polymorphic association that arrises here but am unable to wrap my head around it. In this example 'sourceId' in table 'Reading' would contain the id from one of three tables "ExternalURL", "InternalURL" and "Book". Also, the field "polytable" would contain the table name that the aforementioned "id" came from.

有人可以花点时间解释如何解决这个维持RI的问题吗?还是出于效率考虑应该保留它吗?

Can someone take a moment to please explain how to resolve this maintaining RI or is it possible that in the interest of efficiency that it should be left?

感谢您的时间和考虑，

蒂姆

                       -------------
                      | ExternalURL |
                       -------------
                      | id          |
                      | badlink     |
                      | url         |
                      |             |
                      |             |
                       -------------
                            |
                            |
                            |
                           / \
 ------------          -------------          -------------
| Lesson     |-------<| Reading     |>-------| InternalURL |
 ------------          -------------          -------------
| id         |        | id          |        | id          |
| label      |        | lessonId    |        | url         |
| summary    |        | sourceId    |        |             |
| lessonOrder|        | polytable   |        |             |
| active     |        | label       |        |             |
 ------------          -------------          -------------
                            \ /
                             |
                             |
                       ------------  
                      | Book       |
                       ------------ 
                      | id         |
                      | label      |
                      | summary    |
                      | lessonOrder|
                      | active     |
                       ------------ 

推荐答案

您至少有两个选项可以保留RI:

You have at least a couple of options to preserve RI:

1. 在Reading中添加几个可为空的FK列，每种读取类型对应一个.其中只有一个应该为非null.

1. Add several nullable FK columns in Reading, one for each reading type. Only one of these should be non-null.

CREATE TABLE Reading (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lessonId INT NOT NULL,
  bookId INT NULL,
  externalUrlId INT NULL,
  internalUrlId INT NULL,
  FOREIGN KEY (bookId) REFERENCES Book(id),
  FOREIGN KEY (externalUrlId) REFERENCES ExternalUrl(id),
  FOREIGN KEY (internalUrlId) REFERENCES InternalUrl(id)
);

触发，否则您必须在应用程序代码中执行此操作.但是至少您可以定义外键.

Enforcing that exactly one of the foreign key columns is non-null is a task for a trigger, or else you'd have to do it in application code. But at least you can define the foreign keys.

添加一个超级表Readable，该表是其他所有特定可读类型的父级.

Add a supertable Readable which is a parent of each of the other specific readable types.

CREATE TABLE Readable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  readable_type CHAR(1) NOT NULL,
  UNIQUE KEY (id, readable_type)
);

CREATE TABLE Book (
  id INT PRIMARY KEY, -- not AUTO_INCREMENT
  readable_type CHAR(1) NOT NULL, -- must be 'B'
  FOREIGN KEY (id, readable_type) REFERENCES Readable(id, readable_type)
);

... similar tables for ExternalUrl and InternalUrl...

然后使Reading也引用Readable.

Then make Reading also reference Readable.

CREATE TABLE Reading (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lessonId INT NOT NULL,
  sourceId INT NOT NULL,
  FOREIGN KEY (sourceId) REFERENCES Readable(id)
);

我在对 查看全文