A complicated call or another column in table

whether to total from a seperate table

[proper_bo]

I have two tables.
Table one contains information about a product.
Table two contains comments about all products.

When showing a category of products with the total of comments for each product I could either,

when selecting the products from one table, link the query to count the number of comments for each product
OR
add a number of comments column to the product table and add one to it each time a comment is added.

The first way is a more complicated query but the second involves extra work when a comment is added.

Which would you go with?
If its the complicated query way could you push me in the right direction so that I can select * from the product table and also get the count of comments from the other table.

As always. Thank you for all your help.

[FalseDawn]

It depends on the number of comments which is best, but the first approach will generally be a lot better unless you have millions of comments and need to avoid the overhead of a count.

a suitable query might be
SELECT P.product_id, P.product_name, COUNT(PC.product_comment_id) FROM product P INNER JOIN product_comment PC ON P.product_id=PC.product_id
GROUP BY P.product_id, P.product_name

you will need to adjust based on table names and whatever keys you have and what fields you need returning.

[proper_bo]

Thanks FalseDawn,

I think I will start with a similar query to the one you kindly supplied. If the site starts to drag I can always transfer a count into a new row and go to the other system.

Thank you for your help.