Previous Thread

8/17/2006 1:40:02 AM    table design for customer rates.
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.



8/17/2006 5:48:25 PM    Re: table design for customer rates.
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 
 
-- 
 
Allen Browne - Microsoft MVP.  Perth, Western Australia. 
 
Tips for Access users - http://allenbrowne.com/tips.html 
 
Reply to group, rather than allenbrowne at mvps dot org. 
 
<zubairpam@gmail.com> wrote in message 
 
news:1155804002.012526.154870@h48g2000cwc.googlegroups.com...