I'm trying to design a referral award system that rewards users for referring their colleagues and friends based on the amount of business the referral provides.
For example. John refers Sally. Sally does 100 worth of business, John is awarded 5% to apply to his future use of the service.
I've got that part already down more or less. What I'm struggling with is how to expire those awards if they're not used after a certain amount of time without having to manually update the database everyday.
Lets say John has been given 5 awards over the years:
1) 2013-01-01 $10
2) 2013-05-28 $10
3) 2014-01-03 $10
4) 2014-02-03 $10
5) 2014-05-28 $10
Up to the current date of 2015-05-28, he's not used any of the rewards. But rewards expire after 1 year. On 2014-05-28 he generates an invoice for $50. Because the first reward should have expired, only $40 of the remaining award should be applied to the invoice.
While I could generate a script that would manually 'expire' old rewards on a daily basis, I seem to recall somebody explaining a clever way to select and insert debits into the existing rewards SQL table such that all you ever need to do is know the current date and old miles are automatically excluded and everything balances. Unfortunately I can't find that after quite a bit of searching.
Any thoughts on how to structure this to easily accommodate both usage of the rewards and taking into account expiration of unused rewards after a certain time.
Edit: Here's what I was looking for: Process for handling expiring airline miles
Here's how I've defined the primary rewards table so far:
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| ref_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| transaction_date | date | NO | | NULL | |
| referrer_cust_id | int(10) unsigned | NO | | NULL | |
| referral_cust_id | int(10) unsigned | YES | | NULL | |
| inv_num | int(10) unsigned | YES | | NULL | |
| pay_id | int(10) unsigned | NO | | NULL | |
| ref_rate | float | NO | | NULL | |
| credit_amt | float | YES | | NULL | |
| used_amt | float | YES | | 0 | |
+------------------+------------------+------+-----+---------+----------------+
referrer_cust_id is the ID of the customer who earned the reward, referral_cust_id is the ID of the customer they referred.
I'm basically following the guidance of the prior expiring-airline-miles link which suggests that when a reward is 'claimed', simply sum up the available points that are both 'unused' and 'unexpired'.