Implementation of Comments, Is there any best practice for this?

Question

Well I need to implement the comments feature on a custom coded social networking website. The comments need to be implemented on various pages like videos, pictures, albums etc etc similar to facebook.

What would be the best practice for implementing this ?

Making one global comments table with fields like this, and grab comments based on their comment type on required page.

commentid int(10)
userid int(10)
comment_type ENUM('video','picture')
comment varchat(200)
parent int(10)

or making separate tables for each comment?

Answer 1

I wouldn't do it that way. I would use subtyping on the other side.

• Create a parent table called (for example) Content;
• Video, Picture, Album, etc are child entities of Content;
• Comment has a foreign key of Content ID.

There are three basic schemes for implementing subtyping:

1. Create a separate table for each subtype;
2. Create a table called Content with subordinate tables Video, Album, etc with a foreign key of Conent ID. The Content table also has a Content Type field to indicate what table relates to that record; or
3. Put everything into a table called Content that has a bunch of optional (nullable) columns to cover the subtypes. This is most appropriate when there is little to no difference in the columns required to store subtype-specific information.

If you relate Comment back to a table of Video or Album you have what's called an exclusive arc. This isn't recommended practice for data modelling.

Edit: for example:

• User (id, username, given names, surname, email address)
• Content (id, content_type, submitter_id, submitted_date);
• Image (id, url, height, width);
• Video (id, url, format, height, width, length);
• Album (id, name);
• Album Photos (id, album_id, photo_id);
• Comment (id, author_id, comment_date, comment, content_id);

Some notes:

• submitter_id and author_id are foreign keys for User.id;
• Image.id, Video.id and Album.id are all foreign keys for Content.id;
• Content.content_type is one of "Image", "Video" or "Album";
• An Album consists of one of more Photos that are stored in Image;
• The join table Album Photos links albums to Images (many-to-many relationship);

Hope that clears it up.