I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this;
Sales Channels <---- Allocated Stock Sources <---- Stock Locations (warehouses) <--- Stock Sub Locations <---- Shelf / Bin Locations
As far as rules go for how these entities relate to each other I've come up with this;
The system must have one or more sales channels, each sales channel must have 1 or more stock sources, a stock source must have 1 or more stock locations (warehouses / buildings / distribution centres ), a stock location may have 1 or more Bin/Shelf locations.
A product may have 1 or more stock locations, may have one or more sub locations in those stock locations and may have one or more Shelf / Bin location.
First off, is this a solved problem where some reference schema exists I could utilise and save myself some headaches?
If there isn't a reference design for this situation, Am I best to build a 1 to Many relationship to assign stock to a sub location(s) and another 1 to Many relationship for Shelf Locations (if exists)
This is the ERD I've come up with so far;