我应该将更新的计数作为用户表的一部分吗？

Let's imagine I have a databases with two tables, Users and Posts. The first table contains a row for each user, the second table a row for each post that users have written. If I want to display a post count on the users' profiles, which of these two strategies work the best:

1. Every time a user creates a post I UPDATE the Users table, +1 a field PostCount;
2. When someone visits the profile I simply run a select statement to get a count of post, for example SELECT COUNT(post_id) FROM Posts WHERE id_user = 100;

In the first case I have to UPDATE a table very often, which it could be bad as I believe a table gets locked when doing the update; in the second case I have to run a count every time the user visits a profile. Which poison is the less bitter? Is there any other way?

I would say that it depends on how many times you will display PostCount, especially for a huge amount of Users. If you are going to display it for 1000+ users on a page that will be called a lot of times, then the first solution should be the best. But you need to do transactions to be sure both tables Posts and Users are updated when adding a new post.

Otherwise, the second solution should be enough, but you should use LEFT OUTER JOIN so that you would get both information from Users and Posts table in only one query. Eg:

SELECT * 
FROM Users u
LEFT OUTER JOIN (
    SELECT user_id , COUNT(*) AS posts_count 
    FROM Posts 
    GROUP BY user_id
) p ON p.user_id = u.id
WHERE u.id = :searched_id

(And anyway you should use a Cache system so that you don't have to do the same SQL query for a same page if shown to several users.)