I'm usually pretty good with these types of problems but my head feels pretty dense right now.

I'm working on a project where in the majority of the tables involved, the client wishes to be able to put in 'As Of' data changes. Consider these example table structures :

Sales Person Table
Sales ID
CommissionTable ID

Commission Table Summary
Commission Table ID
Description

Commission Table Detail
Commission Table ID
Sales Volume
Commission Rate

The sales person has a commission table associated with him. A commission table ( Commission Table Summary ) has multiple levels of rates than might be paid depending upon the sales volume ( Commission Table Detail ).

Ok so far I hope?

Now say it's the 31st of the month. The owner wants to change one of the rates in a specified commission table, let's call it table 'A'. He wants this to be effective as of the 15th of the month. So Sales Persons from the 1st through the 14th with commission table 'A' are paid at the old rate, and from the 15th forward, at the new rate.

Now add to that problem, one of the Sales Persons has his commission table changed from table 'B' to table 'A' as of the 10th of the month. So he should be paid from the 1st through the 9th at commission table 'B' rates, the 10th through the 14th at the old commission table 'A' rates, and finally from the 15th forward, at the new commission table 'A' rates.

Is there an elegant structure / algorithm that I'm not seeing, that will allow for point in time reports as well as date range reports to be handled efficiently? The things I'm coming up with don't 'feel' right.

TIA,
Anthony