当两张表非常相似时，应该何时合并？ [英] When two tables are very similar, when should they be combined?

问题描述

我有事件和照片，然后评论两者。现在，我有两个意见表，一个是关于事件的意见，另一个是照片评论。模式类似于：

I have events and photos, and then comments for both. Right now, I have two comments tables, one for comments related to the events, and another for photo comments. Schema is similar to this:

CREATE TABLE EventComments
(
  CommentId int,
  EventId int,
  Comment NVarChar(250),
  DateSubmitted datetime
)

CREATE TABLE PhotoComments
(
  CommentId int,
  PhotoId int,
  Comment NVarChar(250),
  DateSubmitted datetime
)

我的问题是我是否应该合并它们，并添加一个单独的交叉参考表，但我不能想到一个正确的方法。我想这可以，你的想法是什么？

My questions is whether or not I should combine them, and add a separate cross reference table, but I can't think of a way to do it properly. I think this should be OK, what are your thoughts?

修改

根据Walter的回答（和一些光阅读），我已经提出了这一点：

Based on Walter's answer (and some light reading), I've come up with this:

CREATE TABLE Comments
(
  CommentId int,
  Comment NVarChar(250),
  DateSubmitted datetime
  CONTRAINT [PK_Comments] PRIMARY KEY
  (
    CommentId
  )
)

CREATE TABLE EventComments
(
  CommentId int,
  EventId int
)

CREAT TABLE PhotoComments
(
  CommentId int,
  PhotoId int
)

ALTER TABLE EventComments ADD CONSTRAINT FK_EventComments FOREIGN KEY (CommentId) REFERENCES Comments(CommentId)

ALTER TABLE PhotoComments ADD CONSTRAINT FK_PhotoComments FOREIGN KEY (CommentId) REFERENCES Comments(CommentId)

结构之间是否存在任何性能差异？对我来说，似乎有点偏爱。我确实看到了第二个模式的好处，如果我想添加一些特殊的事件评论或照片评论，我有一个单独的表，这样做，如果我想要两个共享一个新的属性，有一个表添加新的属性。

Are there really any performance differences between the structures? To me, it seems like a bit a preference. I do see the benefits in the second schema, if I want to add some specificity to event comments or photo comments, I have a separate table to do so, and if I want both to share a new property, there is a single table to add the new property.

推荐答案

注释，PhotoComments和EventComments以称为泛化专业化的模式相关。这种模式由面向对象语言的简单继承来处理。设置一个可以捕获相同模式的表格模式有点复杂一点。

Comments, PhotoComments, and EventComments are related in a pattern called "generalization specialization". This pattern is handled by simple inheritance in object oriented languages. It's a little more intricate to set up a schema of tables that will capture the same pattern.

但是很好理解。关于泛化专业化关系建模的一个快速谷歌搜索将给你几篇关于这个主题的好文章。

But it's well understood. A quick google search on "generalization specialization relational modeling" will give you several good articles on the subject.

这篇关于当两张表非常相似时，应该何时合并？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！