Hello, I am developing a RoR system to manage a bookstore and I frequently
come to modeling doubts.

My data model to manage sales and stock is just below (with most important
model attributes):

# INVOICE
- reference:string
- supplier:references
- status:boolean (done)

# STOCK
- invoice:references
- product:references

# ITEM
- sale:references
- stock:references

# SALE
- customer:references

Each STOCK record refers to a product that is fisically in the bookstore,
if that record has an ITEM vinculed to it, that STOCK is no more available,
it is sold.
So the conditions to make a STOCK available is to not have a ITEM vinculed
to it, and the INVOICE which it is vinculed to must be done (status true).

To make that logic over this modeling I have did a scope:

scope :available, joins('INNER JOIN invoices ON invoices.id =
stocks.invoice_id')
.joins('LEFT JOIN items ON items.stock_id = stocks.id')
.where('invoices.status = 1 AND items.id IS NULL')
That scope take 2.5 sec. to return 4211 records over 5537 stock records.
When the database grows up I think it could be a problem.
I have tried a scope to get the stock for replacement, I used a NOT IN over
the available stocks, but it take almost a minute to returns. Fail...

Much wrong things with this approach? If the approach is ok, sql views
could be a solution?

Thanks for help.

--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Search Discussions

  • Robert Walker at Dec 8, 2011 at 8:05 pm

    Pedro Fernandes Steimbruch wrote in post #1035754:
    Hello, I am developing a RoR system to manage a bookstore and I
    frequently
    come to modeling doubts.
    That scope take 2.5 sec. to return 4211 records over 5537 stock records.
    When the database grows up I think it could be a problem.
    I have tried a scope to get the stock for replacement, I used a NOT IN
    over
    the available stocks, but it take almost a minute to returns. Fail...

    Much wrong things with this approach? If the approach is ok, sql views
    could be a solution?
    I have developed similar systems in the past and have run into the sorts
    of problems you're now facing.

    I have learned from experience to not rely on the raw inventory data
    records for calculating things like "available" or "quantity on hand."
    Instead what I have done in the past is to add those fields directly to
    the item records and monitor inserts and deletes to the actual inventory
    records. Yes, this does open up the possibility of the quantities
    getting "out of sync" with the actual inventory records. However, the
    benefits of these cached values can greatly outweigh the performance
    problems of calculating those values "on the fly."

    For example finding the "quantity on hand" for an item becomes a simple
    flat lookup:

    product = Product.find_by_item_number("MK12345")
    puts product.quantity_on_hand
    25
    As long as ALL inserts and deletes to the actual inventory records
    updates that quantity field on corresponding Product model all is
    golden, and the system performance remain high.

    Think of it this way. Changes to those quantities occur MUCH less
    frequently than querying those values. This is a great candidate for
    caching. It's similar to the built-in counter caching mechanism provided
    by ActiveRecord.

    --
    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+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Pedro Fernandes Steimbruch at Dec 9, 2011 at 2:55 am
    Thank you, Robert.

    I will try your advice. It will be useful.

    Thanks again.

    --
    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+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Pedro Fernandes Steimbruch at Dec 12, 2011 at 5:05 pm
    Anyone knows another way to do that cache?

    I'm thinking to open that system as a SAAS and I don't want to duplicate
    the book record to each account.

    --
    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+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedDec 8, '11 at 6:11p
activeDec 12, '11 at 5:05p
posts4
users2
websiterubyonrails.org
irc#RubyOnRails

People

Translate

site design / logo © 2021 Grokbase