Best way to structure table for commission reports

I want to have a table that will track all the commissions we are due from our wireless sales. Each plan pays a different Commission, not a problem. We do our reconciliation as sometimes the carriers don't pay (they may miss a line or 2).

The problem occurs when the carriers change how much they pay for the commissions. We want to be able to go back and see that there was 4 lines not paid for in January at commission rate x. but Feb the same types of 开发者_C百科plans were sold but their commission rates were x-10.

If I change the rate in the commission table for the plan, when i go back to pull the data from the January report I will get incorrect commission rates.

I have a table for sales set up with a date stamp so I was thinking i can cross reference that somehow.

Hope someone followed this.

I am looking for the best way to structure this.

You should "STAMP" the record with the commission rate. You could either stamp the actual rate into every record... or you could keep a commission ID and put that into each record.