Multiple database tables within one AR model in Rails 3

Question

I was asked to make some kind of reporting (logging) service. The employee has locally installed web application (just some dynamic website, written in PHP) in many companies. This web app is some kind of survey. All data is saved on local database, but now the requirement is that this data (result of survey) will also be sent to central server after every form submit.

There are four types of surveys. They have organised it this way, that there are many Projects, and each Project can have only one Survey of each type (STI here?) and Survey belongs to one Project. Each Survey will receive a report from local app, so it will have many Reports. The Rails 3 app that logs this reports should mimic somehow this logic. First question is: does this AR structure make sense for you?

  Project-1--------1-Survey-1-------*-Report

  Project
    has_one :survey
    has_many :reports, :through => :survey

  Survey
    belongs_to :project
    has_many :reports

  Report
    belongs_to :survey

Second question is about having multiple tables for one AR Model. If all data will be stored in reports table, the table will become huge very quickly, and efficient querying for reports that belong to specific survey might be a problem after some time. Maybe it would be better to have separate tables for each Survey? Like reports_<survey_id>. Is this possible?

Also, I am somehow forced to use MySQL, but if there is another, much better solution for this, I could try to push it through.

If you are still here, thank you for reading this :)

Answer 1

Second question is about having multiple tables for one AR Model. If all data will be stored in reports table, the table will become huge very quickly, and efficient querying for reports that belong to specific survey might be a problem after some time. Maybe it would be better to have separate tables for each Survey? Like reports_. Is this possible?

Yes, it is possible.

You can do it this way:

class Report < AR::Base
  table_name_suffix = ''
end

Report.table_name_suffix = 'foo'

UPDATE

---

# simple example of sharding model

class Survey < AR::Base
  has_many :reports

  def shard_reports
    Report.table_name_suffix = "_survey_#{self.id}"
    returning(reports){ Report.table_name_suffix = "" }
  end

end

Survey.first.reports_shard

Answer 2

Put an index on each of your foreign keys (e.g. Reports.survey_id) and take a breather. You're worrying entirely too much about the performance right now. You will need at least millions of records in your Reports table before you will see any performance problems from MySQL.