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?


Something like the below should suffice.

     ,COUNT( total_comments
      posts p
     comments c
    ON = c.posts_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.