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;


1 Answer 1


You can check out the book Data Model Patterns by David Hay. It has inventory and product modeling patterns.

It's hard to judge the logical correctness of model without more business details, but I think you should have someone with more experience in data modeling review the ERD so it meets business requirements. You enumerated 1:many relationships, but you modeled many:many which more than meets what's stated.

From a technical perspective, you should not define the product table with PK column id char(36); define the column as product_id int or product_id raw(16) if you really think you have to. RAW is an Oracle data type, use the appropriate data type for your DB platform. This goes for all the tables. Personally, I would not put id in all those "link" tables, it's not necessary.

  • my primary key is a UUID so as far as I know CHAR(36) is the most appropriate format, but I'm happy to be corrected on that so I'll do some research.
    – Matthew
    Nov 2, 2020 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.