Counting Commets

Lets say I have two tables; POSTS table that store posts and COMMENTS table for comments on each post. In a script , I want to list all the post titles from the first table and the corresponding number of comments. What is the correct approach; use a LEFT JOIN with SELECT COUNT(*) or have number_of_comments column in the first table that increments each time there is a new post.

What are the pros and cons.

What if many users are entering comments for a particular post at same time?

Thanks.

Something like the below should suffice.


SELECT
     ,p.title
     ,COUNT(c.id) total_comments
  FROM
      posts p
  LEFT OUTER
  JOIN
     comments c
    ON
     p.id = c.posts_id
 GROUP
    BY
     p.id

The advantage of storing the number of comments in the posts table is eliminating a join. The disadvantage is that doing so adds an unnecessary level of complexity which could go ignored on the application side resulting in the numbers being out of sync. Its really a question of optimization over data integrity. If you ask me I would prefer data integrity and lessening the responsibility on the application layer except in special circumstances. Though a trigger could be used to update the count inside the posts table if you wanted to get fancy and keep the data management contained to the database.