CODEWITHSUNDEEP
ruby-on-railsdatabaseactiverecord
Abdulaziz Alsubaie
Abdulaziz Alsubaie

Reputation: 720

Database count column

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?

Upvotes: 0

Views: 154

Answers (1)

paxdiablo
paxdiablo

Reputation: 881153

"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.

Upvotes: 2

Related Questions