Greetings.
I have an application which is used to track jobs and payments.
The billables table contains columns “job_date”, “amount” and “receipt”
which indicate the date of the job, the amount due and whether or not
payment has been received, respectively. In addition to tracking and
manipulating individual jobs, the application is also used to generate
aggregate information for periods of time, e.g., the sum for jobs in
given months, quarters and years.
I wrote the original version of the application in PHP with
lots of direct SQL calls. When generating the aggregate reports,
rather than selecting all the rows and then crunching numbers in PHP
I created database views which returned the aggregate figures, e.g.,
the monthly view returned the year, month and monthly sum; the quarterly
view returned the year, quarter and quarterly sum, etc.
I am now reimplementing the application using rails. I have a
Job model for working with the individual jobs. What might be the
wisest approach for dealing with the aggregate reports? I have
considered three options:
1) Create the same database views as before. Access these views
directly using the Job.connection object and raw SQL. Coupling the
database
structure to the controller seems like a bad thing though.
2) Create the same database views as before. Create new models for
each database view, e.g., a MonthlyReport model for accessing the
monthly aggregate view. The models should be read-only by nature.
Jobs are more complicated than I’ve explained, and the result is
that the database has a lot of views and therefore a lot of models, but
this should be manageable if I’m thoughtful of the namespace.
3) Discard the views altogether. Retrieve the relevant Jobs
and generate all the aggregate information manually. Note that yearly
aggregate information is frequently accessed, so the application could
retrieve and process tens of thousands of Jobs at a time.
Thanks for any advice you might have.