DB design question for Report generation...

[heat23]

I have a database of affiliates and I pay them a certain amount of money for each client they bring me. However, the amount I pay can vary from month to month for each affiliate. The problem is when I generate historical reports for each affiliate. I don't want to use the current amount being paid, I need to use the amount that was paid during that particular month. So my question is, how can I design the DB in such a way so that I can accomplish this??

Also, if I want to generate a report for lets say, January 2004, how do I convert that to a timestamp and get all the data for that month if I have timestamps stored in the database?

Thanks for the help!

[swr]

Create a table with the following fields:

affiliate | amount_paid | date_of_payment

Whenever a payment is made, insert a row into that table.

To generate a report, do something like this:

select sum(amount_paid) from the_table where affiliate = 'My Special Affiliate' and date_of_payment >= '2003-12-01' and date_of_payment <= '2003-12-31'