database design, best practice, need advice

i have this database, with a table c开发者_如何学运维alled "badges" and "subjects", i have a flag field in "subject" called "is_canceled" bool value, now for some reason someone suggested me to have a field in "badges" called "canceled_subject_count", which has the same value as

"SELECT SUM(is_canceled) as cancel_count FROM subjects WHERE is_canceled = 1 AND badge_id = ".$some_badge_id

one of the reason he said is performance, it is better to take the value straight rather having it to sum first

what do you guys think? should I have this field? and why?

thanks

This is called denormalization. Doing this for the initial database design might save you time and effort later on, but chances are that it's a premature optimization. You're probably better off not adding the canceled_subject_count field and if after some profiling, you find a measured performance bottleneck when counting cancelled subjects for a badge, then you can add the field.

It really depends on the use of the software. This kind of optimization depends a lot on how often the value changes, how many people are viewing it, etc.

If it rarely changes, storing the value and updating when changes occur is probably smarter.

If the sum is changing constantly, it would be smarter just to calculate it with each query.

But unless you're serving to a massive amount of requests, this sum will cost you almost nothing and is probably easier to keep your data integrity in good shape.