Table design receiving partial orders (1 Viewer)

Hi all -

Now I've been tasked with updating a Parts Receiving database. What it really means is a table rework.

I have it mostly worked out but I am unsure of a "best practice" to accommodate partial PO shipments. We buy small hardware from outside sources in quantities in the tens of thousands. This would all be on one PO.

But the shipments might come in lots of 2000 or something and they'll show up on different dates until the total quantity of the PO is reached.

So we may issue a single PO for 20,000 widgets and receive 5,000 on 6/1; 10,000 of them on 6/17; and 5,000 of them on 10/31.

Before I blindly dive too deep into this I thought I would get request some sage advice from those who may have trod a similar road before.

Thanks a bunch for any and all insight and/or direction,

Tim

you need one table for your orders and another for your shipments with a link to the orders table

edit - perhaps more than one

order header
order items
shipment header
shipment items

To elaborate a bit,

You have a PO, the Purchase itself, with things like Date, Vendor, and other authorization information (who, why, what)
You'd have line items with the individual item (by item number) and quantity.

So far, pretty straight forward.
You get a shipment in. The Shipment will normally have tracking number and shipper as well as carrier.
Shipments also have line items.
line item should have supplier, Purchase order, item, and quantity.

You are normally going to only have ONE PO in one shipment, but I'd double check with your receivers to see if you ever get a carrier who brings you 10,000 widgets that match to more than one PO. For the moment, I'll assume no but we can deal with that later if it happens.

You'd then, for each shipment line item, attach it to its corresponding PO line item to show what was actually received. This does mean you can have one (or more) shipment line items for a given PO line item.

Important question, do you need to deal with substitutions? I.e. they no longer sell 5lt kegs of widget juice, but are sending 1lts instead? Having dealt with this for retail, it can be a major headache.

Mark_ said:

Important question, do you need to deal with substitutions? I.e. they no longer sell 5lt kegs of widget juice, but are sending 1lts instead? Having dealt with this for retail, it can be a major headache.

Click to expand...

No, never. All I widgets are our patent. No subs.

Here is a link to Barry Williams' site with a data model for Partial Deliveries. It may give you some insight.

jdraw said:

Here is a link to Barry Williams' site with a data model for Partial Deliveries. It may give you some insight.

Click to expand...

I've only glanced at the model - - - - -but am immediately intrigued by the Products table for a completely different reason - - is it self-referencing?

He is doing one of several ways to "Self Reference", normally found if you have parts that are not common across several products.

I normally use a 2nd table that has "Parent Product" and "Child Product" along with "Quantity". Means you can have a Nut, a Bolt an "Nut and Bolt". Nut as a record becomes a child record to "Nut and Bolt" with a quantity of 1. Same with Bolt.

Also Nut can become a child record to "Kids bracelet" with a quantity of 40 when your kid decides to string 10 of them on a rubber band to make a bracelet. Allows any product to be made of any number of other products while also being a component of any number of other products.

Mark_ said:

He is doing one of several ways to "Self Reference", normally found if you have parts that are not common across several products.

I normally use a 2nd table that has "Parent Product" and "Child Product" along with "Quantity". Means you can have a Nut, a Bolt an "Nut and Bolt". Nut as a record becomes a child record to "Nut and Bolt" with a quantity of 1. Same with Bolt.

Also Nut can become a child record to "Kids bracelet" with a quantity of 40 when your kid decides to string 10 of them on a rubber band to make a bracelet. Allows any product to be made of any number of other products while also being a component of any number of other products.

Click to expand...

I have a very similar condition. I went with the Parent/Child Tables idea. It works great for me.