Jump to content

[Database Design] Comments in multiple locations?


JoeyH3

Recommended Posts

Hello all!

 

I'm beginning to design a website which will be primarily focused on allowing users to upload and share drawings/artwork that they created. I also want to allow users to create profile pages on this site, and be able to post blog entries. Additionally, I want to allow users to be able to leave comments on uploaded images, user profiles, and blog entries. However, I'm having difficulty trying to envision the most efficient way to design the database in order to allow this to happen.

 

At first, I thought of simply having three comment tables - one for image comments, one for profile comments, one for blog comments. However, that just seems inefficient maintenance wise, as if I want to make any changes to the commenting system, then I have to update and test three tables instead of one.

 

Next, I thought of having a single comments table, and having two columns for referencing their parent object - one would be the PK of the blog/image/profile, and the second column would be the type. So then the query for selecting a comment for a journal would look like:

 

select (columns) from comments where reference=(PK of journal) and type="blog".

 

So far, that's been the best idea I could come up with, as that would (likely) guaranty that a comment meant for an image wouldn't accidentally get displayed on a blog entry that ended up having the same PK as the image (I plan on using auto_increment integers on most of the primary keys). However, I just wanted to ask if that is a good way to approach the design, if there were any potential flaws I could be overlooking, or if there is another way that would be better practice?

 

Thanks!

Joey

Link to post
Share on other sites

Alternative options are:

 

A) model the relation and take advantage of the relational model:

 

comments (comment_id)

blog_comments (blog_entry_id, comment_id)

artwork_comments (artwork_id, comment_id)

profile_comments (profile_id, comment_id)

 

because the type column has a few drawbacks which you may not have considered, it's difficult for example to left join using a value in a type column.

 

B) create a comments table for each type instead of just having one comments table.

Link to post
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.