Using multiple tables in MySQL and/or multiple columns for a Rails application

Question

Hello, I'm wondering what the best practice is for having redundant columns in a MySQL database. I'm wondering this because I have a User table in my database but I also want to be able to display many statistics about each individual user. These stats might include how many times a user has interacted with another model or how many messages they have.

Does it hurt performance to search for all records of a certain object (such as messages) in order to count them? Or is it better to make a redundant column in the user table called total_messages that gets updated every time a message is added/removed? Thanks, this has been confusing me for awhile.

Answer 1

The short answer: do both

As a rule of thumb, I don't like creating and updating data that can be calculated on the fly (i.e. counting). Therefore, I would hold off on adding those redundant statistics until you find performance bottlenecks and adjust accordingly.

You can always add retroactive statistics based on user activity, so you could "grow" your application by only adding the redundancy you absolutely need.

Adding redundant data can improve your performance, but it is never a one size fits all solution and you could end up hurting overall performance if you are constantly trying to calculate and update usage values that can be calculated on the fly with a simple request.

Answer 2

What I'd do is to create a view in your database that gives you the relevant information; instead of adding a redundant column, you're adding an optimizable bit of SQL code that LOOKS like a column to the outside world, but which is guaranteed to remain in sync with your data. And the fact that SQL optimizes views makes retrieving the data quite fast.

Answer 3

Check out counter_caches in Rails. They do this for you.

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Something like

class Messages < ActiveRecord::Base
  belongs_to :user, :counter_cache => true

  # ...
end

This would require you to have a column on your users table of messages_count (and would save you from using messages.count)