Inventory modeling with order accounting in Rails

Question

I am developing a commercial rental application with Rails 5. It has a fairly simple structure with users, products, and orders with products owned by users who actually create them.

To allow users to manage the "live" stock of their products, a StockMovement model / table was created with a link to the user and stock changes (positive / negative) and date. Thus, we can determine the stock of a certain product for a certain date in terms of "what the user is ready to offer." With a simple query to one table / model, we manage to get the "amount" of shares, which, as it turned out, is the stock of the product.

In addition, we need to consider when an order is placed and confirmed for a specific product, which leads to the need to subtract the quantity of this item from stock for a certain period of time.

Currently, we are calculating the stock with the request in the StockMovement model and manually adding the amounts that are affected by the orders using the custom select clause in the StockMovement model, which is already starting to feel redundant (we are not even beta).

My question is: how would you implement this Rails? I've always had problems with situations where, theoretically, at least considering the relational logic db (we use Postgres), it’s optimal to calculate everything on the fly using queries with joins and calculated fields, but when it comes to implementing using ActiveRecord, there is no way to refer to the computed column in table B in the query for table A unless you redefine this calculation in the select expression in model A, which I am trying to avoid.

So far, my options, which I now see, are as follows:

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

2- Create an entry in the StockMovement table every time an order is confirmed and processes all stocks from there (IMHO is not desirable, since it needs to be carefully updated every time something changes in orders)

3- A potential magical (and right) decision that I could not think of ...

Thanks!

Answer 1

Personally, I think mixing SQL with ActiveRecord is a Rails way. This is one of my favorite things in AR: you can add the original SQL when you need it. You can even wrap it in encapsulated, reusable methods using scopes . For example, you can define the scope for adding a select column.

You can also create a database view called current_stocks with a single record per product. Get this query stock_movements and orders and calculate the current stock, and then just use it when you need this value. You can even create a read-only ActiveRecord class supported by the view so you can use regular associations, instance methods, etc. Again, Rails offers you a way for advanced SQL features to play well with the rest of your application.

In the end, you will have performance problems if you each time calculate the margin on the fly, so you can (1) make this materialized view and update it periodically in the background or (2) add the current_stock column to products and keep it up to date condition. I would probably go with 2.

Or you could (3) stick to the on-the-fly calculation, but add a starting_stocks table that gives you the value “stocks as of Sunday at midnight” so you never have to figure out the past too far, I think it’s probably the best approach for everyone, and you can postpone its implementation until you really have problems.

If you really want to take a deep dive, you can read about temporary databases. Two good books: developing applications with time-oriented SQL applications by Richard Snodgrass and Bitemporal Data by Tom Johnston. The first is also available as a free PDF file from the author’s website. I suppose this is too much for you, but still it’s great to be aware of.

Answer 2

Think about how to cache the total when you go ahead and save it back to the database. This will be much more effective than collecting all historical records.

Rails has the concept of counter_cache (for more details: http://guides.rubyonrails.org/association_basics.html#options-for-belongs-to-counter-cache ), but this means only entries - not totals.

Fortunately, there is also a stone called counter_culture, which allows you to calculate the total values ​​(see https://github.com/magnusvk/counter_culture#totaling-instead-of-counting )

I don't know if you have a Stock object, but with Stock , which has_many StockMovements definitely feels like the best approach for me. So you still have all the granular movements; Your request is fast; and your counters are kept up to date and accurate in the nature of the database transactions.

Edit: I just read your comment, "I'm interested in the stock price for a specific date." This does not necessarily invalidate this approach, but means that you potentially want to expand it to include something like the StockPosition record, which can be created daily - which records the position any day. Regardless of whether this approach makes sense, it completely depends on how you request the data, and there are several approaches that you could take here, for example. request one StockPosition during the day; Summarizing all historical records up to the set date; or subtract the amount of records back to a specific date from the current Stock total (it can be more efficient than querying all historical data if the dates you are looking at are usually close to today).