Geek asks and answers

How can I allow tripartite polymorphic association?

First let me say that I am using MySQL (not transactional) and that this cannot be changed. In addition, I have simplified the tables here for brevity and clarity.

In this example, the “Lesson” consists of internal attributes and external attributes with its own “Reading” attributes. Readings has its own key-dependent attributes and three different external attributes (reading sources).

I want to avoid the polymorphic association described here, but I cannot wrap it. In this example, the "sourceId" in the "Read" table will contain the identifier from one of the three tables "ExternalURL", "InternalURL" and "Book". In addition, the "polytable" field will contain the name of the table from which the aforementioned "id" came.

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

Thank you for your time and attention,

Tim

                       -------------
                      | 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     |
                       ------------
+5
source share
1 answer

You have at least a few options for saving RI:

  • FK Reading, . .

    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)
);

    , , trigger, . .

  • supertable Readable, .

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

    Readable.

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

    ?

+7

More articles:


All Articles