Are there holes in my super simple referral program (planned)?

Question

I'm planning on writing a referral program for my recent startup. The goal is to entice the current members to recruit new members. Every member of the site will have a referral code and access to materials to help advertise the service.

When a new client signs up and pays, using a current member's referral code, the member who made the referral will receive a one-time payment (roughly 50% of what the client pays at start).

To do this, I'll add 1 field in the user profile table - to store their (generated at signup) referral code.

Then, I'll setup a referrals table to store referrals (date, referral code, new client id). At the end of each month, I'll run a report on the referral table that says who gets paid and how much. Using PayPal I'll make the payments.

Annually I'll run a report for tax purposes and then wipe the DB (to keep size low).

Does this look tight? Are there table fields/data that I didn't list, but should be using? Does it look like it would be hard to exploit this setup?

Answer 1

Do not wipe the DB. When it comes to anything that deals with transactions of money, you need to log everything - updates, edits, changes, insertion, deletion, and there will be charge-backs, the Paypal API failing, database connections not working and so on.

If you do the wipe the DB of referrals, you can't backtrack.

Answer 2

Good point by ExtraKun, you may also need it for tax purposes.

If you want to keep the primary db small, post EOY (End of Year) report archive the data in archiving tables, that way you can provide historic look up functionality (at a later date when and if it become neccessary).