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):
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](http://stocks.id)') .where('invoices.status = 1 AND [items.id](http://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.