Comments on many tables database design issue

I have tables:

Articles{...}
Recipes{...}
Notifications{...}
Photos{...}

And I need to implement 'user comments' feature (like facebook). Should I make tables: ArticleComments, RecipesComme开发者_开发技巧nts etc. with 1:n relationship? Or create one Comments table for all (but I have no idea how to design this)?

You could create another table CommentableEntity (although call it something better). Each of the rows in your tables (Articles, Recipes etc.) would have a reference to a unique row in this table. The entity table might have a type field to indicate the type of entity (to aid reverse joining).

You can then have a Comment table that references CommentableEntity, in a generic fashion.

So for example you'll end up with the following tables:

Articles
-----------------
Article_id
CommentableEntity_id (fk, unique)
Content
....

Recipes
-----------------
Recipe_id
CommentableEntity_id (fk, unique)
Content
....

CommentableEntity
-----------------
CommentableEntity_id (pk)
EntityType (e.g. 'Recipe', 'Article')

Comment
-------
Comment_id (pk)
CommentableEntity_id (fk)
User_id (fk)
DateAdded
Comment 
...etc...

You can add the CommentableEntity record every time you add an Article/Recipe etc. All your comment-handling code has to know is the CommentableEntity_id - it doesn't care what type of thing it is.

That depends on how your application will be using comments.

My guess is that you'll frequently want to pull up all the comments a user has created regardless of the entity that they are commenting on. That is, I assume you'll frequently want a query that returns rows indicating that user JohnDoe commented on Article 1, then Photo 12, then Recipe 171. If that's the case, then it would make far more sense to have a single Comments table with a structure similar to what Steve Mayne has suggested with the CommentableEntity table.

On the other hand, if you would only be accessing the comments for a particular item (i.e. all comments for Article 1), separate ArticleComments and PhotoComments tables may be more appropriate. That makes it easier to have foreign keys between the entity table and the comment table and is potentially a bit more efficient since it's a poor man's partitioning. Of course, as soon as you start having to combine data from multiple comment tables, this efficiency goes away so you'd need to be reasonably confident about the use cases.

The easiest way would to have a 'polymorphic' comments table that would have columns for both the id and the type of the object that it refers to.

The you could do the following:

SELECT * FROM Comments where type = "Articles" and type_id = 1;
SELECT * FROM Comments where type IN ("Recipes", "Photos")

Putting a unique compound index on (type, id) would also improve the performance of the look ups.

SELECT TOP 1000 [Comments_Id]
      ,[Comments_Text]
      ,[Comments_IsApproved]
      ,[Comments_IsVisible]
      ,[Comments_DateStamp]
      ,[Type_Id]
      ,[Entity_Id] -- From Entity Table, listing Articles, Recipes etc. 
      ,[EntityItem_Id] -- One of the PK from table of Articles, Recipes etc.
      ,[User_Id]
  FROM [tbl_Comments]

To have an idea on how to create a single Comments table for all objects, you can take a look at django comment model ( http://docs.djangoproject.com/en/dev/ref/contrib/comments/models/ )