Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Definitely you can make FKs. Performancewise, hard to say anything without knowing about the load of your site. Could you amend your question with this? –  dezso Jul 4 '12 at 14:00
    
The site does not exist yet, no load. You are saying you can make foreign keys even though the field "object_type" will dynamically define which table it refers to? –  nute Jul 4 '12 at 14:16
    
But you probably have plans about how big the load will be :) About foreign keys: how many different types do you have? If not many (say 4-5) and you will have a few hundred thousand rows at least, I would consider adding a separate field (and FK) for each. Otherwise, object_type can be a FK (with its ID) as well. –  dezso Jul 4 '12 at 14:25
1  
No, I don't think this is the correct approach. See this question: Implementing comments and Likes in database You need a supertype table (called Entity in that answer) that the FK in your comment table can reference. –  ypercube Jul 4 '12 at 15:02
add comment

1 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"

share|improve this answer
    
Sounds good. Although I'm not sure I understood "but maintain the object_type (for partitioning later)". –  nute Jul 9 '12 at 12:45
    
object_type would contain a value for what type of object is being commented on. I would recommend tinyint unsigned (1-256) with values like 1 for Question, 2 for Answer, 3 for Comment etc. If the table grows too large you can create the partitions on this column to speed up queries. I would also recommend an index on this column –  ssmusoke Jul 9 '12 at 14:36
    
It is possible to have a restriction in the database (mysql in my case) so only one of the fields be not null? –  PhoneixS Apr 4 at 10:39
    
You can try to use triggers which can lead to a host of problems, however I would recommend putting that kind of logic into the application code or middle layer since its not native database functionality –  ssmusoke Apr 4 at 19:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.