Complex data, simply with embedded calculation?

[Vaughan]

Hello

I'm working on a solution that looks up fees for a service: the company running the database performs the service for several clients, and the fee paid for each service is set by each client and varies between clients.

The rules for the fees are complex and great in number. For example, the fee is based on the postcode (zip) of the location, and the value of the item. They might look like this:

Company 1, postcode 2000 to 2133, value 0 to 100, fee = $10

Company 1, postcode 2000 to 2133, value 101 to 200, fee = $15

Company 1, postcode 2000 to 2133, value >201, fee = $25

Company 2: postcode 2000 to 2133, value 0 to 200, fee = $20

Company 2, postcode 2000 to 2133, value 201 to 500, fee = $25

Company 2, postcode 2000 to 2133, value >501, fee = $55

These need to multiplied for 10 different service types, 16,000 postcodes, at least 10 companies. All up it could be 1,600,000 fee data records!

I am thinking of simplifying this by storing a calculation instead of a single fee value:

Company 1: postcode 2000 to 2133, Case( value ≤ 101 ; 10 ; value ≤ 201 ; 15 ; 25 )

This will require using Evaluate() to calculate the fee when the time comes to work it out... I can cope with that but it will make the fee table non-human readable.

If I do this am I setting myself up for a big fall later? What are the pros and cons of encoding the data as a calculation rather than separate records?

I'm getting massively overwhelmed by the amount of data entry for the fees... they currently exist as 10 page pdfs for each client and the format is usually heavily summarised so it's not possible to simply import the table of data... plus each company summarises their fees differently so it's a manual process to read the fee schedules and work out how to enter them. Then there is the issue of updating them once a year or so.

The postcode ranges are frequently not contiguous, so while 2000 to 2133 is the same value (metro Sydney) 2134 is different, 2135 to 2139 is another value, and so on.

[mr_vodka]

Is the fee composed based on the zipcode fee + the value fee or is it different per each combination for various zipcodes? IOW is metro Sydney a base price for this customer of 10, and then based on value of item additional fees tacked on ( i.e. 0, 5, 15 )