table design for customer rates.

[zubairpam]

Hi,

I'm developing a courier appln which contains the customer rates
different for each customer.
i need to fetch the customer rates from the db. Each and every customer
have different rates for the different countries, cities. I can't
categarize into group bcos of variation in prices with customers. i
give a sample price.

For eg: Cust A

Country Doc Parcel
First 500g Addn 500g First 500g Addn 500g

USA
LA 30 15 40 18

NY 25 12 33 16
Texas 33 16 38 21
other 28 20 30 16


UK 28 20 30 16
SPAIN 27 17 33 17
FRANCE 18 10 26 19
ITALY 20 11 28 13
(max 10 -15 entries)

similarly for 'n' of customers i have seperate price lists varies
depends on country.

currently, i'm using two techniques

1. each customer have seperate tables
2. two tables - one for (CourierType, TypeID)
(DocFirstHalfKG, DocAddnHalfKG, ParFirstHalfKG, ParAddnHalfKG --> 01,
02, 03,04) corresponding
second table :
(TypeID, Countries(rows starting from A to Z all countries like
Algeria,Argentina... Zimbawbe), CustomerID)


If i use the first one, the database increasing tremendously.
In the case of another, many fields are blank.

I'm looking for any best solution to the above problem.
Thanks in advance for your help.

Best Rgds,

Zubair.

 

[Allen Browne]

This really needs a completely different design.

Since you have different rates for each client, for each type of article,
and for each weight category, you will need tables like this:

Zone table (one record per state/country), with one field:
ZoneID Text name of the state/country. Primary key.

ArticleType table (one record per package type):
ArticleTypeID Text "Doc" or "Parcel", or... Primary key.

Customer table (one record per customer):
CustomerID AutoNumber primary key
CustomerName Text

Rate table:
RateID AutoNumber primary key
CustomerID Number relates to Customer.CustomerID
ArticleTypeID Text relates to ArticleType.ArticleTypeID
ZoneID Text relates to Zone.ZoneID
MinWeight Number mimimum weight this rate applies to.
Rate Currency $ to charge this client, for this
type of article, from this weight.

You now have one column in one table to lookup to find the rate for item you
are working with. That's *way* easier than having to redesign the database
to add more tables every time another customer gets added.

Once you have that working, you probably need a very efficient
calculation/retrieval system to get the rate for a specific customer +
article + zone + weight. This article by Tom Ellisoin might help:
Lookup a value in a range
at:
http://allenbrowne.com/ser-58.html