I'm trying to figure out the best way of modelling a basic grocery picking system and something has stumped me. In short: how do I represent a single order that can be split into multiple parts / short orders, to be picked by various pickers?
A little more detail:
For those of you in the UK, think groceries via tesco.com, that's exactly what I'm trying to model, albeit far more simple of course. Here’s how it works (massively simplified) from a dotcom back room in Tesco:
A customer places an order: products, quantities, delivery date.
Customer orders are split into trips (around the store or warehouse) based on various criteria, therefore a single order can be a single trip (rare) or many trips.
A member of staff picks an order (or part of) via a trip. Another member could be picking for the exact same order, just a different part of it. When picking a trip, you can be picking for multiple orders at once, one huge order, two normal orders and a larger one. Real world example: Six trays on a picking trolley, each tray is a single customer order (or part of) and it’s possible for more than one tray to be part of the same order, so items can be mixed between the two. You could have six different customers, or 1 customer across 5 trays and another separate customer. Any mix.
I can handle the makeshift splitting rules, what I can’t wrap my head around, is how to represent the split orders in a simple database. I've attached an image of what I think it should be like.
This is all for fun on my part. I'm just trying to build a mock ’enterprise’ .NET app as a learning experience. There’s a million ideas I have that I can add to this that would be great to show off, alas database design is a massive slog for me.
Any ideas how I would tackle this? I've googled warehouse picking, online shopping and all the rest. It's either not what I need, or way too complex. I really want to start writing some code.