Mysql – Foreign key with multiple table options

Architectureforeign keyhierarchyMySQL

I'm not sure if the title is very good, feel free to suggest a better one.

People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, an Answer or a Comment).
The way I've decided to design it is something like this:

CREATE  TABLE IF NOT EXISTS `comment` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Who voted' ,
  `object_type` VARCHAR(45) NOT NULL COMMENT 'The table or class name of the voted object. Such as \"answer\", \"question\", ...' ,
  `object_id` INT UNSIGNED NOT NULL COMMENT 'The primary key of the object being voted on' ,
  `value` TINYINT(1) NOT NULL COMMENT '+1, -1 or 0 for a comment' ,
  `content` TEXT NULL COMMENT 'A free text comment about the object' ,
  `votes` INT UNSIGNED NULL DEFAULT 0 COMMENT 'How many votes did this comment receive' ,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_vote_user` (`user_id` ASC) ,
  CONSTRAINT `fk_vote_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Keeps track of \"upvotes\" or \"downvotes\" of content.'

Probably too much info for here but basically I'm taking the approach of a generic "object_type" VARCHAR ("QUESTION" for example) and an "object_id".

Is this a good approach? Can I make foreign keys? What would you suggest to improve performance?

I am using MySQL and PHP (and Yii).

Best Answer

Instead of object_id and object_type columns, why not use a primary key for each object type to be commented on, but maintain the object_type (for partitioning later) so that you have question_id, comment_id, and answer_id. For each row of data, two of the columns will be null, but it is easy on your ORM (single table inheritance with a type qualifier).

Your queries will also join to the correct FK, I am not sure of the space usage for this, but I expect it to be very performant since the joins do not need additional filters on the where clause for different types as well as complex "SQL join acrobatics"

Related Question