#RubyOnRails database and app design question in the vicinity of inventory management:
-
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.
@jgarber I would absolutely use a join model for this (and spend some time coming up with a good name for it)
-
@jgarber I would absolutely use a join model for this (and spend some time coming up with a good name for it)
@sstephenson The naming conundrum is ruining me!
-
@jgarber I would absolutely use a join model for this (and spend some time coming up with a good name for it)
@sstephenson “InventoryAllocation”…?
-
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.
@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.
-
@sstephenson “InventoryAllocation”…?
@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!)
-
@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.
“should SKU belong to a warehouse?”
I don’t believe so, no. SKU here would be for categorization and reporting, as you noted.
-
@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!)
@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…?
-
@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…?
@jgarber yeah, I like that, nice parity
-
@jgarber yeah, I like that, nice parity
@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.
-
“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.
-
@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.
@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.
-
@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?
-
@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?
@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.
-
@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
-
#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 modelAn 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.
@jgarber I feel like I’m missing something. SKU is a type of thing, InventoryItem is a thing, and Warehouse is a place.
So to count thing you do select count(id) from inventory_items group by warehouse, sku.
Or did I read that completely wrong?
-
R relay@relay.mycrowd.ca shared this topic
-
@jgarber I feel like I’m missing something. SKU is a type of thing, InventoryItem is a thing, and Warehouse is a place.
So to count thing you do select count(id) from inventory_items group by warehouse, sku.
Or did I read that completely wrong?
@pointlessone Nope, I don't think you're missing anything!
-
@pointlessone Nope, I don't think you're missing anything!
@jgarber if that’s too slow see if you can add some indices, if still slow wrap Rails.cache around it.
