When two tables are very similar, when should they be combined?
data-modeling, database-design, normalization, sql-server
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.
Problem
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.