Modelling stock considering orders in Rails

Question

I am developing a rental marketplace application with Rails 5. It has a quite simple structure with users, products and orders, with the products belonging to users, who actually create them.

In order to let the users manage a "live" stock of their products, a StockMovement model/table has been created, with reference to the user, and the stock change (positive/negative) and a date. This way, we can determine the stock of a certain product for a specific date, in terms of "what the user is prepared to offer". With a simple query to a single table/model we manage to get the "sum" of the stocks, which happens to be the stock of the product.

Apart from that, we need to consider when an order is placed and confirmed for a certain product, resulting in the need of substracting an amount of that item from the stock for a period of time.

Currently, we calculate the stock with the query in the StockMovement model, and manually joining the amounts affected by orders, using a custom select clause in the StockMovement model, which is beginning to feel overkill already (we're not even beta).

My question is, how would you implement this the Rails way? I've always had trouble with such situations where, theoretically, at least with relational db logic in mind (we use Postgres), the optimal thing is to calculate everything on the fly using queries with joins and calculated fields, but when it comes to implementing it with ActiveRecord, there is no way of referencing a calculated column in table B in a query to table A, unless you re-define that calculation in a select statement in model A, which is what I'm trying to avoid.

So far, my options as I currently see are:

1- Keep things the way they are: repeat the calculation logic in the select statement in order to access "foreign calculated fields"

2- Create a record in the StockMovement table every time an order is confirmed and handle all stock from there (not desirable IMHO, as it needs to be carefully updated every single time something is modified in the orders)

3- The potential magic (and right) solution I haven't been able to think of...

Thank you!

Answer 1

Personally, I think mixing SQL with ActiveRecord is the Rails way. It is one of my favorite things about AR: you can inject raw SQL when you need it. You can even wrap it up in encapsulated, re-usable methods by using scopes. For instance you could define a scope for adding that select column.

You could also create a database view called current_stocks with one record per product. Have that view query stock_movements and orders and compute the current stock, then just use it whenever you need that value. You can even make a readonly ActiveRecord class backed by the view, so that you can still use regular associations, instance methods, etc. Again, Rails is offering you a way to make advanced SQL features play nice with the rest of your app.

Eventually you are going to have performance problems if you're computing the stock on-the-fly every time, so you could either (1) make that a materialized view and refresh it periodically in the background or (2) add a current_stock column on products and keep it up-to-date. I would probably go with 2.

Or you could (3) stick with on-the-fly computation but add a starting_stocks table that gives you a "stock as of Sunday at midnight" value, so that you never have to compute too far into the past. I think this is probably the best approach of all, and you can postpone implementing it until you actually have problems.

If you really want to make a deep dive, you might want to read about temporal databases. Two good books are Developing Time-Oriented Database Applications in SQL by Richard Snodgrass and Bitemporal Data by Tom Johnston. The former is also available as a free PDF from the author's website. I'm guessing that is overkill for you right now, but still it's a great thing to know about.

Answer 2

Consider caching the total as you go along, and saving that back to the database. It'll be far more efficient than having to sum all historic records.

Rails has the concept of a counter_cache (more info: http://guides.rubyonrails.org/association_basics.html#options-for-belongs-to-counter-cache), but that only counts records - not totals.

Luckily there is also the counter_culture gem, which does allow you to count totals (see https://github.com/magnusvk/counter_culture#totaling-instead-of-counting)

I don't know if you already have a Stock object, but having a Stock that has_many StockMovements definitely feels like the optimal approach to me. This way you still have all of the granular movements; your querying is fast; and your counters are kept up-to-date and accurate by the nature of database transactions.

Edit: I've just read your comment "I'm interested in the stock value for a particular date". This doesn't necessarily invalidate this approach, but means that you potentially want to extend it to include something like a StockPosition record - which could be created daily - that records the position on any given day. Whether or not this approach makes sense depends entirely on how you'll be querying the data, and there are multiple approaches you could take here e.g. querying a single StockPosition for a day; summing all historic records up to a set date; or subtracting the sum of records back to a particular date from the current Stock total (can be more efficient than querying all historic data, if the dates that you lookup tend to be quite close to the present day).