Reference to various tables

Question

Well, I'm in a middle of some design issue. I'm trying to built kinda universal commenting system for my site. So I started with two tables:

Comment_node has many Comments

Now I want to be able to attach my cool comment system to various places on my site. For example to blog_posts and to user_pages.

blog_posts has one comment_node

user_pages has one (different) comment_node

So I ended up in idea of storing additional comment_node_id field in blog_posts and user_pages tables. But the problem here is in the fact that this connection is unidirectional - I can get Comment node from either blog posts and user pages, but having comment_node I cannot find which other table uses it.

Sure I may store 'linked_table' string in comment_nodes or something, but I suppose that will kill my database design.

Is there a nice way to achieve this? thanks

Answer 1

Actually your idea is correct. Alter your Comment_node table and add following columns

| commentable_type | commentable_id |

The commentable_type column contains a string with the name of the referenced table (from your example, either blog_posts or user_pages) and the commentable_id contains the id of either the blog_post or a user_page (depending on the commentable_type column).

Then add a foreign key to both the blog_post and user_page table to reference your Comment_node.

I've designed these kind of comment systems before, usually my approach is

commentable_type [0..n] <--> [1] commentings [1] <--> [0..n] comment

My commentings table corresponds to your Comment_node (if I'm assuming this correctly). In my opinion this is the best way to achieve this.

EDIT: You could then perform a left join as follows:

SELECT * FROM Comment_node c
LEFT JOIN Blog_post b
ON c.commentable_id = b.id
WHERE c.commentable_type = 'blog_post'