Comment system design

Here is my current comment system design:

I'm developing it for a website that has lots of areas, blogs, tutorials, manuals etc etc. As supposed to developing a separate comment table for each (tblBlogComments, tblTutorialComments) etc etc, I'm trying to go for a one structure fits all approach.

This way, I can turn the comment system into a web control, and just drop it on any page that I want comments for. It means I only have one set of rules, one set of code files to maintain.

The only problem is, is coming up with a 'nice' way to determine which section (blog/tutorial/manual) belongs to.

For example, one solution would be:

tblComment
-------------
Section (int)
SectionIdentifier (int)
开发者_JS百科

Where 'Section' maps to a unique to each part of the site, EG:

Blog = 1
Articles = 2
Tutorials = 3
...

A SectionIdentifier is some sort of unique ID for that page, eg:

ViewBlog.aspx?ID=5

This would be section 1, identifier 5. So now, a comment with Section = 1, SectionIdentifier = 5 means it's a comment for blog entry number 5.

This works great, but at the cost of maintainability, and a solid structure, as the SectionIdentifier is anonymous and no relationships can be built.

Is this design OK, or is there a better solution (IE some sort of parent table for a comment?)

In Codd's original designed for the Relational Model, a foreign key could reference multiple primary keys in different tables, and the referential integrity was valid if any one table contained the value.

Unfortunately, SQL is a pale reflection of that original vision, since it does not provide this ability, as you have noted.

One standard work-around is to create a new relation that holds the keys to all of the others. But that's not a very good solution in this case, since it creates a point of contention if lots of inserts are happening at once.

The way I would handle this is to create a value—let’s call it a Comment-Anchor—that you can put into every table that is to have comments. This value (unlike all the other keys in a well-designed database) should be a GUID. Then each comment can have a Comment-Anchor that indicates which value it is in reference to.

By making it a GUID, you can always insert unique values in your blog or tutorial or whatever, without contention. You do not have to maintain a master-list of Comment-Anchors anywhere, and no section contends with or is blocked by any other section.

This will work well for the normal use-case of finding all the comments for a single blog entry, for example. To go the other way, from comment to the thing that is being commented on, you could put a flag in the comment table identifying which table is being refrenced, but I wouldn't do that. I would just search all the tables, maybe with a view or something. The reverse query would be rare enough, that I don't see much point in maintaining infrastructure for it, and the flag would be redundant data, which is the bane of RDBMSs.

One additional benifit of this system is that it is easily extensible. If you create a new type of data, or decide to add comments to an existing type of data, then you need only add the Comment-Anchor column to the table. No additional work must be done on the database side. And even the middleware portion that handles the comments does not need to be modified in any way, since it has no knowledge of what sorts of things take comments.

For a table design, I would model it as closely as possible to what the class structure seems to be in this case. From what you have said, this is what it looks like (roughly):

Section <- Post <- Comment

So, you'd have:

1. a Section table (eg. blog, articles, tutorials, etc.)
2. a Post table (for the individual posts in each section)
3. a Comment table (for the comments on each post)

Each post would have a reference to it's section, and each comment would have a reference to it's post. The DB could have the references as nice, clean foreign keys, and the classes could have lists on one or both sides of the relationships as your app needs them.

To me, that seems like a nice, simple, flexible structure that doesn't complicate things and still allows you to hang extra bits like edits and votes off of it.

I would steer clear of creating an id column that defines a different relationship depending on another column in the same table. For instance, in your example SectionIdentifier could represent any number of foreign key references depending on the value of Section. That skeeves me out on general principle. It also leaves several benefits of modern RDBMS platforms on the table since it's not supported.

How is your general architecture for these different sections layed out? I've worked with a few CMS's that would require each of your sections to share a common base entity, calling it a "module" or "plug in". Each instance of a given module then has it's own id, which is used to map to any content required by that specific instance.

If this is a viable architecture direction for you, you could also use that ModuleInstanceID as the foreign key for your comments. You'd just have to decide how you register a given type of module/plug in as being a valid target for comments.

Anyway, can you shed a little light on how your sections are put together under the hood?

It seems that. Your comment system consists of many kind of comment (tblBlogComments, tblTutorialComments..... etc). I would like to suggest you to adopt strategy design pattern.

Let's say. You have a IComment interface. And All kind of comment class implements IComment interface.

interface IComment
{
    int ID {get; set; }
    int Section  {get; set; }
    ....
}

class BlogComment : IComment
{
    ....
}

class TutorialComment : IComment
{
    ....
}

And a WebControl which only knows how to deal with IComment

class WebControl
{
    IComment _comment = null;

    public WebControl(IComment comment)
    {
        _comment = comment;
    }
}

Of course you need a CommentCreater to loads comment data from database and builds the comment object.

public static void main()
{
    var creater = new CommentCreater();
    IComment comment1 = creater.CreateBlogComment()
    WebControl webcontrol = new WebControl(comment1);
    ......

    IComment comment2 = creater.CreateTutorialComment()
    webcontrol = new WebControl(comment2);
    ........
}

That way your web control can just treats all kind of comment in the same way. No matter exactly what kind of comment it is. and you can also just maintain the CommentCreater to build each kind of comment class correctly.