Mastodawn

Jason Garber 2d ago

#RubyOnRails database and app design question in the vicinity of inventory management:

• Your app has a Warehouse model,
• a serialized InventoryItem model, and
• a SKU model

An InventoryItem belongs to a SKU and belongs to a Warehouse. Over time, InventoryItems move between Warehouses but the association to a SKU is static.

Out there in the real world, you're managing inventory at multiple locations •and• need to know precisely which InventoryItems (based on serial numbers) are in which Warehouses.

How would you design an efficient means of tracking not only which InventoryItems are in which Warehouse (fairly easy with associations) but also tracking the •count• of each SKU at each Warehouse?

So, something a bit more complicated (as far as I can think about it, at least) than a typical `belongs_to` and `counter_cache` setup.

I don't know of a "polymorphic counter cache" kind of thing.

A JSONB column on the Warehouse model also feels less-than-ideal.

A join model between the Warehouse and InventoryItems models is an approach. The counter cache could be on the join model. That'd still involve including/joining the join model when the app wants to display inventory counts along with a Warehouse but would also avoid manually fiddling with counters as InventoryItems move from Warehouse to Warehouse.

Sam Stephenson 2d ago

@jgarber I would absolutely use a join model for this (and spend some time coming up with a good name for it)

Jason Garber 2d ago

@sstephenson The naming conundrum is ruining me!

Jason Garber 2d ago

@sstephenson “InventoryAllocation”…?

Sam Stephenson 2d ago

@jgarber maybe there’s an associated physical process you can reference? could something like “Stock” work?

my other tips are to make sure to include timestamp columns, because you will probably want them for some reason or another, and don’t forget your indexes

(also, InventoryAllocation isn’t bad!)

Jason Garber 2d ago

@sstephenson There’s a related model I didn’t mention, RestockingLevel, whose most useful attribute is minimum allowable quantity for a particular SKU at a particular Warehouse.

It’s closely related to what I outlined in the problem statement, but isn’t exactly the same. Its utility is more in triggering action when certain conditions are met.

So maybe “StockLevel” for this new join model…? 🤔

Sam Stephenson 2d ago

@jgarber yeah, I like that, nice parity

Jason Garber 2d ago

@sstephenson It was right in front of me the whole time!

Appreciate your feedback on this one.

I’m responsible for an app that’s growing beyond its initial and rudimentary data model. I’m beginning to see a path out of the current problems, but it’ll involve some tricky data migrations in addition to the foundational modeling improvements.

@jgarber should SKU belong to a warehouse? If InventoryItem represents a single physical thing, THAT belongs to a warehouse so you know where it is. It has a SKU so for categorization and reporting, but a SKU and warehouse should only relate through items.

Jason Garber 2d ago

“should SKU belong to a warehouse?”

I don’t believe so, no. SKU here would be for categorization and reporting, as you noted.

@jgarber stitch fix had this exact model and we counted items per SKU when needed. The primary DB did not have reporting data cached on it. So a count of items in a SKU per warehouse was not modeled in Rails explicitly.

Transactional logic in our case never needed to know the counts. Only reporting and that was not needed realtime.

Much of our complex transactional queries were in SQL-way easier to understand and maintain than Active Record.

Jason Garber 2d ago

@davetron5000 The app I'm working on operates at a fraction of a fraction of a percent of the scale of StitchFix. 😅

Strictly internal app for managing serialized inventory between various locations out there in the real world and making sure we restock depending on configured conditions, contractual obligations, etc.

So, in this case at least, the reporting aspect (knowing how much inventory is where and when) is part of the core function.

There's also a bunch of knock-on data-related tasks that the team's pulling out from a replica database and using some off-the-shelf dashboard kind of thing. I tend not to have much to do with that, though.

@jgarber Yeah, the last place I worked was at a tiny scale, but I still put non-normalized data into separate tables. Maybe more infra than you want, but I had migrations for reporting tables + models in the rails app, and then regular tasks to query the normalized data and populate the reporting stuff. Even in that case, the reporting was needed at most daily, so again realtime was not a factor.

I just balk at the rails "auto update some count" thing because how could you ever trust it?

Jason Garber 2d ago

@davetron5000 You know, that’s a good call out that’s got me thinking: How “real time” does my app’s statistical data (inventory counts at locations, etc.) really need to be?

Rhetorical in this conversation, but something for me to consider with the folks at work tomorrow.

On your last point: I actually don’t mind counter caches! I haven’t (yet) gotten bitten by one on an association but that could be a consequence of the small scale I’m operating at. Or, I’ve missed some problems. 😂

@jgarber just takes one update in db console and your counts are off :)