"ArtūrasB."
<arturas.baranauskas-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote in
post #991441:> Could you please recommend elegant way (read - best practices in
> "Rails way", less DB calls) to solve the following situation ?
>
> I have 2 tables:
>
> Meter
> - has_many :meter_records
>
> MeterRecord
> - belong_to :meter
>
> I have to calculate difference between meter''s current month
record
> and same meter''s previous month record.
>
> I would like to avoid unnecessary selects from DB and also avoid
> dealing with SQL - I prefer functionality provided by ActiveRecord.
Having some experience with this sort of thing, I would consider adding
a summary table to your database. It may seem counter-intuitive since
we''re aways told to avoid data duplication, but in the case of
reporting
I often make this exception. There can be incredible performance
benefits to be gained by flattening, and pre-calculating data used in
reports.
I would create a simple flat table that records monthly summations for
each customer. This makes reporting fast and efficient. I would keep the
normalized data as well for showing detailed transaction reports.
You''ll
need good automated tests to ensure your model logic always keeps the
data in sync, and possibly a batch process for regenerating the summary
data in case it ever does get out of sync.
This may be overkill for your application. It really depends on how many
customer records you expect to have. The implementation is pretty
straight forward. Every time you add a new meter reading, you also
update the summary. The safest way to do that is to use the AR aggregate
features to sum the details records and replace the summary value. As
opposed to adjusting the sum by adding the value to the previous sum.
This should be fast enough to be unnoticeable to the user when performed
on an individual customer''s records. Also, don''t forget to
adjust the
summation for updates and deletes along with inserts.
This could even be implemented as database triggers if you want to
ensure that this happens no matter whether it''s your application doing
the updating or some external application performing the changes.
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.