creating a database for tracking shipments - design help needed

[Chris Bassett]

So I try to design a system that can allow us to track shipments.

Here is my basic criteria/constraints:

1. Each shipment must have at least one tracking number (depending on the carrier, each package will have its own tracking number so a shipment very well could have multiple tracking numbers associated).

2. For truck carriers, they use one tracking number for a shipment. A shipment can consist of multiple orders (this is part of my problem).

3. ONce used, tracking numbers cannot be repeated

4. ONce an order is shipped, it cannot be shipped again


The issue I'm trying to overcome is how to create the primary key/unique relationships so that a tracking number not being used more than once except for where several orders maybe combined into one truck freight shipment. But if (for example) being shipped by UPS or Fedex, there would be a separate tracking number PER BOX not per order.

Initially, my thought was to use the tracking number and order number pair as a composite primary key, but the problem with this idea is that
you could accidently associate the same tracking number with a different set of orders by accident, and it would still be allowed. (I'm aware that I'm going to need to put some logic into the frontend application to help control this), but from the perspective of creating tables, can anyone suggest a method for going about this?

I was thinking something maybe like this (I only included the pertinent fields, I left out fields that won't matter in the overall conceptual design):

------

Orders
OrderNumber (PK)
CustomerID
IsShipped (boolean/bit)

Shipments
ShipmentID (PK)
ShipDate (date)
CarrierID (FK Carriers table)

Tracking
ShipmentID (FK Shipments table)
OrderNumber (FK Orders table)
TrackingNumber (PK)

(In the Tracking table, ShipmentID, OrderNumber, and TrackingNumber would make up the primary key/composite key).

-----

This is what I can come up with, but sort of don't like the idea of having a ShipmentID for each individual shipment (would prefer to use existing
fields if possible)