If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. wrote in message oups.com... 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. |
Thread Tools | |
Display Modes | |
|
|