"ArtūrasB." <arturas.baranaus...@gmail.com> 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@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
