About database modeling.

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](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.

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.

Thank you, Robert.

I will try your advice. It will be useful.

Thanks again.

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.