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:

  `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' ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_vote_user` (`user_id` ASC) ,
  CONSTRAINT `fk_vote_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
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).

  • 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. Jul 4 '12 at 15:02

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"

  • 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 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 '14 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 Apr 4 '14 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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