Database count column

Question

I have two tables:

"sites" has_many "users" 
"users" belongs_to "sites"

Is it better that whenever a users got added to sites I added column called users_count in sites table and increment it by one. Or is doing a conditional count on users table the best way?

if the count's being done MANY times, then it'd make sense to store a cached value. But low traffic/low query-rate, the extra overhead might not be worth it. — Marc B
– Marc B, Commented Apr 5, 2012 at 4:59

paxdiablo · Accepted Answer · 2012-04-05 05:01:04Z

2

"Better" is a subjective term.

However, I'll be adamant about this. There should not be two sources of the same information in a database, simply because they may get out of step.

The definitive way to discover how many users belong to a site is to use count to count them.

Third normal form requires that every non-key attribute depends on the key, the whole key, and nothing but the key (so help me, Codd).

If you add a user count to sites, that does not depend solely on the sites key value, it also depends on information in other tables.

You can revert from third normal form for performance if you understand the implications and mitigate the possibility of inconsistent data (such as with triggers) but the vast majority of cases should remain 3NF.

answered Apr 5, 2012 at 5:01

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Database count column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related