SQL : keep count in row or select count from db

Question

example : i have 2 tables
- Categories
- Posts

is it a good way to keep post number in categories like this

categories

 id |  title   | posts
----+----------+--------
 1  | golf     |  50
----+----------+-------
 2  | soccer   |  90
----+----------+-------

posts

 id |  title   | category_id
----+----------+--------------
 1  | news 1   |  1
----+----------+--------------
 2  | news 2   |  2
----+----------+--------------
 3  | news 3   |  1
----+----------+--------------

or i use select count() in queries like this

SELECT c.id,
       c.title,
       count(p.id)
FROM `categories` c
INNER JOIN `posts` p ON c.id=p.category_id
GROUP BY c.id

but the problem is when i keep count in categories table when post change category i have to update posts field in categories table too. it's no problem in small project but for big project what is the good way to deal with count because i concern about database performance

thanks for all answers

Answer 1

My personal preference would be not to keep duplicated data in any table, until it has been proven necessary. If you are averse to writing JOIN queries, you could define a view that contains the query and you can then forget about it.

I have found in the past that proper indexes usually mean there isn't too much of a performance problem with this.

If you find it necessary to keep a count summary your categories table (for performance or other reasons), consider creating INSERT, UPDATE and DELETE triggers on your posts table so that updates can be done by the database rather than relying on the application programmers to remember what has to be done.