Polymorphic associations in SQL

polymorphic.sql

-- How to map this in Doctrine to have "commentable" entities (subclasses of Commentable)
-- associated to many comments?


-- These 2 tables are both "commentable"

CREATE TABLE IF NOT EXISTS `posts` (
  `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255)
  `body` TEXT
);

CREATE TABLE IF NOT EXISTS `projects` (
  `id`   INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255)
  `etc`  TEXT
);


-- Comments can associate to ANY entity with the right mapping, by matching on commentable_id and commentable_type

CREATE TABLE IF NOT EXISTS `comments` (
  `id`                INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `commentable_id`    INTEGER NOT NULL,
  `commentable_type`  VARCHAR(64) NOT NULL,

  `body`              TEXT,
  `author_id`         INTEGER NOT NULL
);
CREATE INDEX `index_comments_on_commentable` ON `comments` (`commentable_id`, `commentable_type`);
CREATE INDEX `index_comments_on_commentable_type` ON `comments` (`commentable_type`);