问题描述
这是我现在的评论系统设计：
我正在开发一个有很多领域，博客，教程，手册等的网站。每个表（ tblBlogComments ， tblTutorialComments ）等等，我试图去一个结构适合所有的方法。
这样，我可以将评论系统变成一个Web控件，并将其放在任何需要注释的页面上。这意味着我只有一套规则，一组要维护的代码文件。
唯一的问题是，提出了一个好的方式来确定哪个部分（博客/教程/手册）属于。
例如，一个解决方案是：
tblComment
-------------
Section（int）
SectionIdentifier（int）
其中'节'映射到网站的每个部分都是唯一的，EG ：
Blog = 1
文章= 2
教程= 3
...
SectionIdentifier 是某种唯一ID该页面，例如：
ViewBlog.aspx？ID = 5
这将是第1部分，标识符5.所以现在，一个评论与 Section = 1 ， SectionIdentifier = 5 意味着它是一个针对博客条目5的评论。
这个工作很好，但是以可维护性为代价，一个坚实的结构，因为 SectionIdentifier 是匿名的，不能建立任何关系。
这个设计是OK还是有一个更好的解决方案（IE某种父母表的评论？）
在Codd的原始设计的关系模型，外键可以引用不同表中的多个主键，如果任何一个表包含值，引用完整性是有效的。
不幸的是，SQL是一个简单的反映那个原始的愿景，因为它不提供这种能力，正如你所注意到的。
一个标准的解决方法是创建一个新的关系，其他。但是，在这种情况下，这不是一个非常好的解决方法，因为如果许多插入是一次发生的，它会产生争议点。
我将处理的方法是创建一个值 - 让我们将其称为注释锚点，您可以将它放入每个要注释的表中。该值（与设计良好的数据库中的所有其他键不同）应为GUID。那么每个注释都可以有一个Comment-Anchor，它表示它引用的值。
通过使其成为GUID，您可以随时在您的博客中插入唯一的值教程或其他任何内容，无争议。您不必在任何地方维护评论锚点的主列表，任何部分都不会与任何其他部分竞争或被其他部分阻止。
这将适用于例如，查找单个博客条目的所有注释的正常用例。要从另一个角度来说，从评论到正在评论的事情，你可以在注释表中放一个标志来标识哪个表被拒绝，但是我不会这样做。我会搜索所有的表，也许有一个视图或某事。反向查询将是不够的，我没有看到维护基础设施的重要性，标志将是冗余数据，这是RDBMS的祸根。
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?)
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.
这篇关于评论系统设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！