table design for customer rates.  
Author Message
ifaenson





PostPosted: Thu Aug 17 04:54:02 CDT 2006 Top

SQL Server Developer >> 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.

SQL Server232  
 
 
AugustinPrasanna





PostPosted: Thu Aug 17 04:54:02 CDT 2006 Top

SQL Server Developer >> table design for customer rates. Zubair,

I would stick to below design as it gives extensibility to accommodate
future changes in Business. I would like to hear other's comments on this.

Customer
----------
CustomerId - 01
CustomerName - customerA

Country
----------
CountryId - U.S
CountryName - USA

CourierType
--------------
CourierTypeId - D P
CourierTypeDesc - Doc Parcel

Weight
---------
RangeId - 1 2
MinRange - 0 501
Max Range - 500 10000


Price
--------
CustomerId - 01 01
CountryId - U.S u.s
CourierType - D D
WeightId - 1 2
Price - 30 15

Regards,
Augustin



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