1

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.

possible order schema

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.

  • Picks could simply be OrderID, StaffID, ProductID. Staff members can determine what they need to get and where it should go once retrieved. Shouldn't need anything else to either get it or deliver it to the proper order. Probably need a lot more tracking fields throughout the model – Dave Sep 21 '15 at 15:22
  • well, I'm looking for a little automation that I can develop, staff need to be told what to get, that's sort of important. They receive a list of items (that make up many orders / part orders) that looks like one long shopping list. Where it goes doesn't matter really (essentially goes directly to a van). – LaMoulin Sep 21 '15 at 16:03
  • When I said "where it goes" I meant which order it is returned to. The three IDs I listed are all you need to split an order to different staff, find what is needed, and to return it to the appropriate order. – Dave Sep 21 '15 at 16:24
  • I don't have time to give a full answer, so I'll just offer: New table OrderPart that contains 1..n records per Order. – AakashM Sep 22 '15 at 9:23
  • ah okay thanks Dave, that makes sense. AakashM, OrderParts makes sense also, as I'd want to keep track of each part of the order (say a customer complains, good to know who picked it!) and keep track of the entire pick/trip (multiple order parts) a member of staff is picking... right? @Dave, what do mean tracking fields? – LaMoulin Sep 22 '15 at 12:34
0

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.

  • thank you. outside the scope of this question, but: as a rule of thumb, should I be computing values within the DB? I.e. total number of retrieved items, is something can be done via counting records or via code beforehand. – LaMoulin Sep 22 '15 at 18:02
  • For this you probably don't want to do a calculated column... so I'd just do a lookup in your DB for the total on insert unless you already have that information in your app for a picker... might be safer as a DB lookup to eliminate timing issues from the app... in the case of two split second retrieval rows being written. The two apps might not have time to refresh the total before requesting a write. – Dave Sep 22 '15 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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