6

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.

scottm
  • 27,829
  • 22
  • 107
  • 159

4 Answers4

9

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Best pure design comment on the page, IMHO. – Rap Sep 25 '09 at 16:20
  • I did some research and updated the question. Thanks for the input – scottm Sep 25 '09 at 18:05
  • I strongly disagree with this answer; in practice, you're begging for inconsistent, messy data. How are comments deleted? I frequently see it set up like ScottM implemented it in the revised question, and almost always the comment stays in Comments, but the related record in EventComments or PhotoComments is deleted, and you're left with a dangling record in Comments. – JasonFruit Jul 21 '10 at 20:49
4

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
Gratzy
  • 9,164
  • 4
  • 30
  • 45
  • Yeah, that was the problem I was running into – scottm Sep 25 '09 at 15:54
  • 1
    They relate to two different "entities" I don't think they belong together – Gratzy Sep 25 '09 at 15:55
  • Although they are related to different entities, they are both comments. – scottm Sep 25 '09 at 15:58
  • That's true if it where me though I would keep them separate. I'm not a fan of soft key structures they get very messy. – Gratzy Sep 25 '09 at 15:59
  • I guess it would depend on whether "They are all comments" is more important, or whether conceptually it is always just "Event comments" or "Photo Comments" – Robert Harvey Sep 25 '09 at 16:00
  • @Robert I think that's the key in my case. The comments won't ever be mixed in anyway, and will always be separate. – scottm Sep 25 '09 at 16:02
1

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

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
1

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.

Tom A
  • 1,662
  • 2
  • 23
  • 41