Database Design - Courier shipping information

Need a bit more information...

what is a "weight band"?
are there many locations for each courier?
is there a customer (and address) associated with the destination?
is all payment recivied/payment type information to be excluded?
i am assuming you are starting from scratch or adding to an exsisting db?


-Navicerts

Weight bands:
EG Up to 1Kg.
1Kg - 2Kg
2Kg - 5Kg
etc etc

Not sure re courier destinations, for the time being will only be incl UK, Europe & US using Royal Mail information, but will be adding UPS, FedEx etc at a later date.

Yes, already have detailed customer information tables, however all Country location info links to tblCountry which contains comprehensive list.

Payment information already recorded elsewhere and not to be included here.

Adding to an existing DB.

seems like it would be more important what office something came from rather than just looking at the courier.  For instance if soemthign is shipped fedex it would make a big diffrence if it was shipped from a fedex in the US as opposed to a fedex in the UK.  If this is not the case you can use CourierID in place of OfficeId and remove the OfficeId info.


tblCourier
--------------
CourierID(PK)
OtherCourierInfo

tblCourierOffice   <---- for couriers with multiple locations
--------------
OfficeID(PK)      
CourierID(FK)
Address
Phone
OtherStuff

tblShipment
--------------
ShipmentID(PK)
OfficeID(FK)
Destination(FK)  < -----country code maybe (dont fully understand what is exsisting already)
WeightBandID(FK)
ShipmentMethod   <---- De-normalized,i imagine it's fairly static unless your going to start shipping by camel

tblWeightBand
--------------
WeightBandID(PK)
WeightRange             <----- not sure if it would ever be the case but you can change price AND weight ranges this way.
Price



Hope this helps, or at least gives idea's if not.

-Navicerts

I never realized that shipping prices worked that way, it would be much simpler it seems to me if you could simply plug in the weight of the item to an equation and get a price.

One thing i forgot to add in is the date, this would obviously be important because if one day shipping prices changed and you wanted to look at old prices they would all be wrong if no date was involved :)

Another thing i was thinking is that i was assuming that the weight ranges would never change(weight bands).  Although a date into the WeightBand table should fix this problem as well.

-Navicerts

Thanks Navicerts,
This looks pretty close to what I need.
I'll give it a go first thing in the AM tomorrow.

Must be quick, don't have much time right now :-)


Comments:
I greatly prefer your table naming to Navicerts:
no "tbl" prefix (major)
use plural rather than singular (very minor)


Some q's:

Shouldn't the specific weight of the pkg be in the Shipments table?
Shouldn't there be something in the Shipments table to link back to the original order?  Note, too, that one order may require more than one shipment.

Shouldn't the weight band depend on shipment method?  
That is, Royal Post may have different bands than FedEx.  Meaning that the key to WeightBands table would include ShipmentMethod.

ScottPletcher,
Thanks for the input.
The individual product weights are stored in tblProducts.
But yes I think you are right, that the total weight of the shipment should also be included in tblShipment.
tblSalesLineItems contains details of each order item and its delivery destination.
The complete order details such as payment method and invoice address etc are stored in tblSalesOrder.
So a foreign key from tblShipment to tblSalesLineItems is probably required.

However, regarding the weight bands, you have a valid point.
Would I not also have to link the weight bands table to  the courier table?

I was sticking with table names that begin with "tbl" because you seemed to have that already in your original post.

I definetly agree with scott on the weight band depending on the shipment method but im unclear a bit by the example, shipment method would be (air, ground, exc) and courier would be (fedex, poyal post) the way i understood it.  But i would actually think it could depend on both of those things?  And in addition perhaps packages from diffrent offices of the same courier would have diffrent weight band prices as well?

If this is the case courier (or offices depending on where the prices very) as well as shipment method would need to be included in tblWeightband


Probally better to keep things in the singular as well.

-Navicerts

>> I was sticking with table names that begin with "tbl" because you seemed to have that already in your original post. <<

I totally missed that.  Where was it?

just figured it based off this line "I already have a comprehensive country list in tblCountry"

Oops, you're right, that seems plausible.