modelling grocery orders, that can be split into multiple short orders 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.
Your model is almost there for your stated purposes, so as I suggested in comments Picks doesn't need too many fields to capture the basic information you need to both accomplish splitting an order to different people, and tracking when/how it was completed.

While I mentioned the basics above, what you also really need are tracking/audit fields. With these you can actually monitor the status and recover history of who picked what and when. I'd suggest process audits and system audits. With both you can track issues with the software solution and your business process (allows some basic support for things like connectivity loss of scanners in a warehouse, system down etc.).

OrderID INT NOT NULL
StaffID INT NOT NULL
ProductID INT NOT NULL
ItemRetrievedFlag bit NOT NULL
ItemPickDateTime DATETIME NULL
InsertSystemID INT NULL --something like a barcode scanner device ID
AuditInsertDate DATETIME NULL
AuditUpdateDate DATETIME NULL
UpdateSystemID INT NULL


With a table like this you can retrieve order fulfillment history as simply as:

SELECT *
FROM Picks
WHERE OrderID = X


The query above gives you all recorded activity for the order, but it may not tell you what state the order as a whole is in. You should also have some kind of an OrderTracking table(s).

OrderID INT NOT NULL
StatusID INT NOT NULL
StatusDateTime DATETIME NULL
OrderTotalRetrievedItems INT NULL
OrderTotalItems INT NULL
AuditInsertDatetime DATETIME NULL
AuditUpdateDateTime DATETIME NULL
AuditUpdateSystemID INT NULL


You may find that with a consistent order fulfillment process you can instead dedicate columns to the status, but this pushes you into an update pattern instead of inserts as orders move through the process. If you keep a single row per status change you can more fully Denormalize your design.