I'm working with a database that has a straightforward one to many relationship for customers, orders, line items and shipments.

 

The structure in place is :

 

Customers => Orders => Line item <= Shipments

Shipments are created from the order records and tagged accordingly with the unique identifier for the order. Unshipped line items from that order are visible via portal on the shipment record. As they are added to that shipment they are in turn tagged with the unique identifier for the shipment. 

 

This works great and without issue. However, we have recently had several requests, from customers who have multiple open orders, to consolidate all the unshipped items across their various orders into one shipment. 

 

Is there a way to do this without also consolidating the order records into one? For record keeping purposes, I would prefer to keep the individual orders unique.

you'll need a new join table between orders and shipments because now you have a many-to-many relationship between orders and shipments

Hi Wim, 

 

Thanks for the reply. If I set up a many-to-many relationship between orders and shipments, what is the breakdown for the primary and foreign key relationships?

 

Right now, my set up is _pkOrderID => __fkShipmentID

 

How do I alter the __fkShipmentID field so that can belong to multiple orders? Is it as simple as tagging the various orders with the shipping record id instead of the inverse? Or do I need to establish a new field? 

 

Thank you.

You need to add a new table, call it JoinOrdersShipments (JOS)

 

The relationship then goes like this:

 

 

 

 

 

ORDERS          JOS              SHIPMENTS    
-----------------------------------------------
               _pkJosID
_pkOrderID ==> _fkOrderID
               _fkShipmentID <== _pkShipmentID

 

 

 

As a side note:

In your post you mention a relationship between _pkOrderID and __fkShipmentID, that's not the right way to do it.  I would have expected a relationship between _pkOrderID in Orders and _fkOrderID in Shipments.

Thanks, Win. I'll give it a shot.

 

You're side note is correct. That is how I have it set up not as I previously wrote. 

 

I'll let you know how it goes.

I just wanted to follow up and say that everything worked out perfectly. It took me a little bit to figure out how to manage the lineitems so that I could still split the order contents across shipments but it is all working fine now.

 

Thank you so much for the help.