CODEWITHSUNDEEP
sql-serverdatabase-designdata-modelingnormalization
scottm
scottm

Reputation: 28701

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.

Upvotes: 6

Views: 666

Answers (4)

Gratzy
Gratzy

Reputation: 9389

If you combine them it's going to mess up the key structure. You will have to have null-able foreign keys or a "soft" key structure of key and type. I'd keep them separate.

Upvotes: 4

Walter Mitty
Walter Mitty

Reputation: 18940

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.

Upvotes: 9

Tom A
Tom A

Reputation: 1682

My own personal design style would be to combine them, then add an integer flag to tell what the comment is for. That would also give me scalability in case I want to add more later.

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180788

You can combine them and add a field that indicates whether it's for a photo or an event.

You will need two foreign keys; one for photos and one for events, but having them in one table allows you to write a single set of code to handle all comments.

But I'm torn. It is cleaner if you keep them separate, provided you never have to mix the two comment types in the same list (which would require a UNION).

Upvotes: 1

Related Questions