472,328 Members | 1,072 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

table design - varying contract rates


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.
Oct 8 '07 #1
1 1454
3,080 Expert 2GB
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 ?

Oct 8 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Bryan Guilliams | last post by:
I'm trying to come up with an elegant, simple way to compare two consecutive values from the same table. For instance: SELECT TOP 2 datavalues...
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike,...
by: Jeff Kowalczyk | last post by:
I need to adapt this an update statement to a general form that will iterate over multiple orderids for a given customerinvoiceid. My first concern...
by: Stewart Allen | last post by:
Hi all, I'm designing a club database and have encountered a problem when trying to extract the total amount of fees that a Student/Family is...
by: gacuna | last post by:
i want to insert into a temporal table the result of a store procedure. on sql server the sentence would look like this (already working) INSERT...
by: PeteCresswell | last post by:
I've got something called "Reference Rates". The idea is that on a given day, we have various rates of return for various entities. e.g. Libor...
by: antheana | last post by:
Hi there, I am currently redesigning a database, which includes a contracts table that captures information about each contract e.g. ...
by: tasmontique | last post by:
I am working on an access 2002 flight schedule database. I am new to access but have some basic understanding of sql and vb6 code. I have learned...
by: Brad Pears | last post by:
Here is a simple OO design question... I have a Contract class. The user can either save an existing contract or they start off fresh with a...
by: tammygombez | last post by:
Hey fellow JavaFX developers, I'm currently working on a project that involves using a ComboBox in JavaFX, and I've run into a bit of an issue....
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.