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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
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>> 25As 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.