Design Question

Here is my scenario:
I'm creating a thin client viewer/editor for a client that has a rebate
program.
There are several types of rebate programs, and incentives are different for
each. There are approximately 160 predicating companies and each "reports"
it's sales amount or other qualifying criteria each quarter.
Now, all this is pretty vanilla until, except that the rebate percentage for
each company can change in midstream of a fiscal year.
The old records (pre percentage) change must be kept for viewing only, but
post change must be editable and updateable.
What is the best way to accomplish this without having to nest a lot of
tables?
Thanks in advance for you input..
~ChrisI'd track the old and new percentage rates in a table and based on your
query date (Run from date) you select the proper percentage rate from
that table to use for the calculation.
Ex.
PercentageValidDate Percentage
1/1/2000 6.5
1/1/2003 6.85
6/1/2003 6.93
SELECT TOP 11 Percentage where PercentageValidDATE < [paramater run date]
order by PercentageValidDATE DESC
Smitty wrote:
> Here is my scenario:
> I'm creating a thin client viewer/editor for a client that has a rebate
> program.
> There are several types of rebate programs, and incentives are different for
> each. There are approximately 160 predicating companies and each "reports"
> it's sales amount or other qualifying criteria each quarter.
> Now, all this is pretty vanilla until, except that the rebate percentage for
> each company can change in midstream of a fiscal year.
> The old records (pre percentage) change must be kept for viewing only, but
> post change must be editable and updateable.
> What is the best way to accomplish this without having to nest a lot of
> tables?
> Thanks in advance for you input..
> ~Chris
>