Search code examples
sql-serverdatabase-designdata-modelingnormalization

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?

Edit

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.

Solution

  • 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.