table design - varying contract rates

[gareth]

Hello

I am looking for some help with table structure design, i am creating a database which manages data on loft and cavity wall insulation done by various contractors. The database uses look up tables to work out how much energy is expected to be saved over the lifetime of the householder in GWh by the individual measure installed using data like the type of house and the type of fuel used to heat the home. The monetary value of the work is actually worked out on the basis of the GWhr's saved.

At the moment there is a single table TBL_Agreements which holds the information relevant to the contracts we have with our partners who do the actual work and recieve payment from us. There are two types of work, PG and ATP and the table has two important fields PoundsPerUnitPG and PoundsPerUnitATP. The monetary value of the work is then easily worked out by multiplying the number of energy units (GWhr's) by the appropriate pounds per unit figure.

Now the situation has been made slightly more complicated our partners are being offered incentive rates for the PG work because we aren't getting enough of it. This is being offered in the form of a bonus so they get (say) 7500 £/GWh up to x GWh, then 8000 £ / GWh up to y GWhrs then 8500 £/GWh after that. I'm am wondering how to implement this in the database and the idea I have come up with is to have a table TBL_Rates which is linked to TBL_Agreements in a one to many relationship so one agreement can have many rates (or it could of course only have one and that special case would be the old style flat rate £/GWh agreement).

My original table had the following fields:

IDAgreement (autonumber primary key), IDAgent (foreign key to agents - read contractors, table), IDScheme (another foriegn key), Agreement (text label, name for the agreement), PoundsPerUnitPG, PoundsPerUnitATP, PercentPG (how much of the work in total must be PG by the end of the contract term), FromDate, TermInMonths, BudgetPG (in energy units), BudgetATP, BudgetUnit(the energy unit GWhrs or CarbonTonnes), Notes, FixedPriceAgreement (don't worry about this for now it just makes it more complex).

Now I am thinking of basically taking PoundsPerUnitPG and PoundsPerUnitATP and putting them in this new linked table TBL_Rates along with also a FromDate and ToDate (though FromDate and Term would also stay in TBL_Agreements) and also a FromGwhrs and ToGWh's then when the data is imported i can calculate the GWh's saved as usual and then work out with a query which band i need to use to calculate the moneys

just wondering if this sounds like a good idea to the more experienced posters amongst us or if someone has done anything similar and has a better way.

[nico5038]

I would construct besides the standard tblRates I would define a table holdong the Bonus.
Reason for this is the fact that the bonus can vary and having it recorded in a table will allow easy updates. Even when the rates should be recorded depending on a specific period, this will allow the addition of a validity date.

The tblBonus would hold:
WorkType (PG or ATP just in case this is added in the future)
LimitStart (Lower GWh starting with 0 )
LimitEnd (Higher GWh ending with 9999999999)

As a range is always needed, also the 8500 and higher will have to get an upper limit, being the max value the number field can hold.

For extracting the needed Pounds you can use a JOIN where the calculated GWh us used and looks like:
WHERE GWhOrder Between LimitStart and LimitEnd

Getting the idea ?

Nic;o)