Best Way to do this

[TG]

I am trying to figure out how to code for this scenario:

WebFedExZones

WebFedExZoneID int
FedExZone int can have nulls which indicates we cannot
ship there
ZipCodeStart varchar(3)
ZipCodeEnd Varchar(3)


WebFedExRates

WebFedExRateID int
Weight int
FedExZone int corresponds to the FedExZone in WebFexExZones

ShippingCharge money

We only ship to Zones 2 – 8.

To encapsulate this, we need a new class with one function to
determine the shipping charge given a zip code and weight.

This Has to be done in a vb class for asp.net

 

[Martin Dechev]

Hi,

First: two db design advices:
1. Don't use null for FedExZone to which you don't ship - use 0 or -1
instead.
2. Change ZipCodeStart and ZipCodeEnd to smallint - there are no "less than"
or "bigger than" comparisons on strings (or varchar values in the db for
that matter).

Then you will have to query the DB 2 times:
1. First, you will need to divide (integer division) the zip code for the
dellivery by 100 to get the first 3 digits. Then the select will look like
this:

SELECT FedExZone FROM WebFedExZones WHERE (ZipCodeStart <= @zip AND
ZipCodeEnd >= @zip)

If the FedExZone is ok continue, else return that the shipment isn't
possible.

2. Now you should find the money which the client should pay for the weight.
As I don't know what your WebFedExRates table's entries look like, I presume
the following table:

ID Wgt Zone Charge
1 0 2 10
2 10 2 16
3 20 2 19
4 30 2 21
5 40 2 23
6 50 2 25
................................

I also presume that for package with weight of 8 the client should pay the
price for 0, for 15 - the price of 10, etc. So:

SELECT * FROM WebFedExRates WHERE FedExZone = @zone AND Weight = (SELECT
MAX(Weight) FROM WebFedExRates WHERE FedExZone = @zone AND Weight <= @wgt)

Hope this helps
Martin