Can I link two foreign keys using a junction table.

Posted on 2009-02-10
Medium Priority
Last Modified: 2012-05-06
I have 4 tables as shown in the attached picture.

I want to insure that each delivery only contains packages from the same shipment.  So I added the "ShipmentID" foreign key as the other primary key in the "Packages" table.  In my junction table "DeliveryPackages" I shared the foreign key "ShipmentID", so it is a foreign key to Packages and Deliveries.  I would like to know is this in normal form, if so is it at least 3rd normal form.  I have left out other fields to simplify the question.
Question by:dyzenment
  • 6
  • 5
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 23602201
<<I want to insure that each delivery only contains packages from the same shipment.>>
Not sure I understand that.  What's the difference then between a delivery and a shipment?
I would assume that packages belong to a shipment, and multiple shipments could be made within a delivery...

Accepted Solution

dyzenment earned 0 total points
ID: 23602914
A shipment is a group of packages with a common destination.  A delivery is a group of packages that were actually transported.  Packages belong to a shipment and multiple packages will belong to a delivery.  Deliveries may not be the best name for the table, it really represents a group of packages that will be sent per mode of transportation.

Author Comment

ID: 23602949
For example.

One shipment includes 5 packages.  2 packages get sent through FedEx and the other go on a courier for immediate delivery.  So I would have 1 shipment, 2 deliveries, and 5 packages.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 22

Assisted Solution

dportas earned 1000 total points
ID: 23603009
With only the names to go on I would hazard a guess that the candidate keys are as follows (not entirely clear from your diagram though):

Shipments: (ShipmentID)
Packages: (PackageID)
Deliveries: (DeliveryID)
DeliveryPackages: (PackageID, DeliveryID)

Assuming the following FDs are valid:
DeliveryID -> ShipmentID
PackageID -> ShipmentID
(PackageID, DeliveryID) -> Quantity

then DeliveryPackages is not in BCNF.
DeliveryPackages is in 3NF if ShipmentID is a candidate key in that table but not otherwise.

Normalized or not, your design may still be valid in my opinion. Most DBMSs make it hard to enforce multiple table constraints (other than foreign keys) so if a business rule is important enough then a little denormalization may be the price to pay for enforcing it. I have done the same thing to solve an almost identical problem.
LVL 58
ID: 23603379
"One shipment includes 5 packages. 2 packages get sent through FedEx and the other go on a courier for immediate delivery. So I would have 1 shipment, 2 deliveries, and 5 packages."
then I don't understand why you want to do this:
"I want to insure that each delivery only contains packages from the same shipment."
I think it would be much clearer to say:
"I have 1 order, which includes 5 packages. 2 packages get sent through FedEx and the other go on a courier for immediate delivery. So I would have 1 order, 5 packages, sent in 2 shipments."
Given that, I can't see the need for a delivery table. In your scheme, deliveries and shipments amount to the same thing.
What would be different is if you had the setup of something like a furniture company where you would have multiple orders, which might be fulfilled with multiple shipments, and the shipments might be combined to form a delivery run.
For example:
order 1 - shipment 1 - delivery 1
order 1 - shipment 2 - delivery 2
order 2 - shipment 1 - delivery 2
order 2 - shipment 2 - delivery 3
order 2 - shipment 3 - delivery 4
So you had the second shipment for order 1 and the first shipment for order 2 go out on delivery run 2. In this case, there would be multiple shipments on a delivery.
In any case, it seems from what you describe that deliveries and shipments should be combined and there would be no need for a linking table. "From" and "To" in the shipment table really belong with the order. The only time they would not is if a single order could be sent to multiple addresses. But I have yet to see such a system in twenty three years of IT. All systems I have seen would break the master PO down into multiple orders with one ship to per order and link the orders via PO number to relate back to a master PO.
LVL 58
ID: 23603568
scratch most of that...I'm getting to hung up on the title of the tables.  Understand what it is your trying to do now.
You need to remove ShipmentID from DeliveryPackages.  By having ShipmentID in the Deliveries table, you've made the statement that there can only be one shipment per delivery.  Hence you don't need it in the DeliveryPackages table, because any package in this delivery must belong to that one ShipmentID.
LVL 58
ID: 23603606
and to add to that, a PackageID should only be able to appear once in the DeliveryPackages table for a given delivery ID.   Deliveries and DeliveryPackages should be joined on DeliveryID, so you need to add DeliveryID to the DeliveryPackages table.
I still don't think the setup is right though because with something like Fedex or UPS, each individual package may have it's own tracking number.

Author Comment

ID: 23603874
I'm sorry, the diagram cut off a field.  There should be a Quantity field in the Package table also.  The primary keys are

Shipment table

Package table



The FDs are
DeliveryID -> ShipmentID -> Many to 1
PackageID -> ShipmentID -> Many to 1
PackageID -> DeliveryID -> Many to Many (must share same shipmentID)

I will probably use software to verify that the quantity in the junction table does not exceed the quantity in the package table.

If I get rid of the ShipmentID in the DeliveryPackages then you technically could have packages from other shipments linked to a delivery.  The ShipmentID makes sure that the package and the delivery has the same shipment.  and yes each delivery only have one shipment.

So I need to make the ShipmentID a key in the DeliveryPackages table?  Does it matter that it is a foreign key to both Packages and Deliveries?  What would be the difference to go to BCNF.

Author Comment

ID: 23603883
Also, I plan to put more than 1 package in a box to fedex.
LVL 58
ID: 23604950
I have to say, I'm getting confused over the nomenclature being used and what point of the shipping process we are at. For example, does table Packages represent the cartons on an order or are these the actual packages all ready to be shipped (possibly master packed already).
And in your design is PackageID a surrogate key/auto increment or is it actually the nth package number within a shipment?

Author Comment

ID: 23606231
the packages represent the cartons on an order.  PackageID is auto increment.
LVL 58
ID: 24106372
I'm sorry, I lost track of this.  dyzenment, are you still working on this?  If not, please feel free to delete the question as I never did bring a solution to the table.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question