Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1203
  • Last Modified:

Database Design - Courier shipping information

I need some help designing some addtional tables to go into my e-commerce database.
I  need to add the following information:
Courier name
Shipping method
Courier's worldwide postage rates by weight bands.
I already have a comprehensive country list in tblCountry
But what is the best method to use (lookup tables etc) to get this postage rate info into the dbase?

I imagine a table for Couriers,
1 for CourierDestinations (containing CourierID & CountryID),
1 for Weightbands containing generic lower and upper boundaries of weight bands,
and 1 for ShippingRates containing CourierDestinationID, WeightID and rate.

However, as I am keen to keep this database as normalized as I can, I'm not sure the best way to implement this.
Can anyone advise?
0
ilikemycar
Asked:
ilikemycar
  • 6
  • 3
  • 3
1 Solution
 
NavicertsCommented:
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
0
 
ilikemycarAuthor Commented:
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.

0
 
NavicertsCommented:
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

0
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.

 
NavicertsCommented:
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
0
 
NavicertsCommented:
tblCourier
--------------
CourierID(PK)
OtherCourierInfo

tblCourierOffice  
--------------
OfficeID(PK)      
CourierID(FK)
Address
Phone
OtherStuff

tblShipment
--------------
ShipmentID(PK)
OfficeID(FK)
Destination(FK)  
WeightBandID(FK)
ShipmentMethod  
Date

tblWeightBand
--------------
WeightBandID(PK)
WeightRange            
Price
Date
0
 
ilikemycarAuthor Commented:
Thanks Navicerts,
This looks pretty close to what I need.
I'll give it a go first thing in the AM tomorrow.
0
 
Scott PletcherSenior DBACommented:
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.
0
 
ilikemycarAuthor Commented:
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?
0
 
NavicertsCommented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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?
0
 
NavicertsCommented:
just figured it based off this line "I already have a comprehensive country list in tblCountry"
0
 
Scott PletcherSenior DBACommented:
Oops, you're right, that seems plausible.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now