Hello everyone. This is my first post here, and it's probably going to be a ong one. Thanks in advance for reading...
I'm working on a small project for a transport company. This company has many clients, each of which has a different pricing structure. The way pricing is calculated is like this:
'Price band' is calculated using the postcode of the delivery address.
The price of the job is then calculated at present using a lookup table containing a 'price band' field and several fields containing price-per-parcel to that 'price band'. If that doesnt make sense (which it probably doesnt), here is a diagram:
| Customer Number | Number of Parcels | Delivery Postcode | Price |
So, customer X wants to send 6 parcels to postcode NW1 for example.
The price is calculated like this:
Postcode NW1 = Price band 4, lets say
Price band 4, for 6 parcels (looking along row in Price-per-parcel lookup table where price band=4) which is between 4 and 8, is, lets say $3 per parcel.
$3 x 6 = $18.
Its a relatively simple process, but the problem is that there are many customers (around 100), each of which will have a Price-per-parcel lookup table., as the contents of that lookup table are different in each case.
What I would like to know is this:
Is there a more efficient way of storing the data so it is quicker to glean a price?
Really, I would like to know if anyone has any suggestions about the best way to go about structuring the tables so that th querying can be simple and fast.
Thanks again for taking the time to read this one. If it doesn't make a lot of sense, please let me know and Iwill answer any questions I can.