Comment system design

Question

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)

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?)

Answer 1

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.