Which is better database design?

Question

Given a site like StackOverflow, would it be better to create num_comments column to store how many comments a submission has and then update it when a comment is made or just query the number of rows with the COUNT function? It seems like the latter would be more readable and elegant but the former would be more efficient. What does SO think?

Answer 1

Definitely to use COUNT. Storing the number of comments is a classic de-normalization that produces headaches. It's slightly more efficient for retrieval but makes inserts much more expensive: each new comment requires not only an insert into the comments table, but a write lock on the row containing the comment count.

Answer 2

The former is not normalized but will produce better performance (assuming many more reads than writes).

The latter is more normalized, but will require more resources and hence be less performant.

Which is better boils down to application requirements.