Multiple database tables in one AR model in Rails 3

Question

I was asked to do some kind of reporting (registration) service. An employee locally installed a web application (just some kind of dynamic website written in PHP) in many companies. This web application is a kind of survey. All data is stored in a local database, but now the requirement is that this data (the result of the survey) should also be sent to the central server after each form of sending.

There are four types of surveys. They organized it in such a way that there are many projects, and each project can have only one overview of each type (STI here?), And Survey belongs to one project. Each survey will receive a report from the local application, so it will have many reports. The Rails 3 application that logs these reports must somehow mimic this logic. First question: does this AR structure really make sense to 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 

The second question concerns the availability of several tables for one AR model. If all the data is stored in the reports table, the table will become very fast, and efficiently requesting reports related to a particular survey can be a problem after a while. Maybe it would be better to have separate tables for each survey? Like reports_<survey_id> . Is it possible?

In addition, I somehow have to use MySQL, but if there is another, much better solution for this, I could try to skip it.

If you are still here, thank you for that :)

Answer 1

The second question concerns the availability of several tables for one AR model. If all the data is saved in the report table, the table will become very fast, and efficiently requesting reports related to a specific survey can be a problem after a while. Maybe it would be better to have separate tables for each survey? Like reports. Is it possible?

Yes it is possible.

You can do it as follows:

 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

Place an index for each of your foreign keys (e.g. Reports.survey_id) and take a break. You are now too worried about performance. You will need at least millions of records in the Reports table before you see performance issues from MySQL.