Re: Need help with DB design/model

[Peter Brawley]

* Each carrier uses a tracking number system (so, no order is shipped without a tracking number)

* You can have one or more orders that are shipped on one single tracking number (this is the part I'm having a problem with)

* Once a tracking number is used, it cannot be re-used

Lots of possibilities. Assuming an orders table PK orderID and no chance one carrier's tracking# can duplicate another's ...

create table ordertracking( 
  trackingno varchar(64) primary key, 
  orderID, 
  foreign key(orderID) references orders(orderID) 
);

If cross-carrier duplication can't be ruled out ...

create table ordertracking( 
  carrier varchar(32),
  trackingno varchar(64), 
  primary key(carrier,trackingno),
  orderID, 
  foreign key(orderID) references orders(orderID) 
);

Or if there's a carriers table with carrierID PK ...

create table ordertracking( 
  carrierID int,
  trackingno varchar(64), 
  primary key(carrierID,trackingno),
  orderID, 
  foreign key(orderID) references orders(orderID) 
);