User Referral System using PHP and MySQL

Question

I already have a fully pledged membership system and want to integrate my own user referral system but want some tips, advice really on doing it.

My current registered users table is something like

users (table)
- id (user id)
- email (user email)
- password (hash of users password)
- reg_date (date user registered)

I have some other fields but not relevant to what I want to do and to keep things as simple as possible I left them out.

I am thinking of creating a new table called referrals and for example when a registered user on my site from users table refers someone (I guess I would create a form that asks for email of person they want to refer and their name and add that to referrals table) it then displays in there account who they referred and how many people they referred and if the person they referred joined up to my site.

I am hoping if someone could possibly explain the logic of what I need in my referrals table to track the referrals?

My table so far looks like this:

referrals (table)
- uid (id of referrer, this would be a registered user from users table)
- ref_count (total the referrer from users table has referred people)
- action (maybe not rite name) (if the person who was referred completed signup)
- I guess some id or something for the person being referred, not sure and what ever else I may need.

Obviously on signup page I would have a referrer text box that user can enter persons username they was referred by and checks to see if they was referred by the already registered member and i guess in a referral email link i would add user who made referral and do a simple check on sign-up page something like signup?ref=username_here

and if ref isset via get add the username automatically into text box.

As you can see I am struggling with the logic behind the referrals table and would love it if someone could explain what else I would need? and logic behind what I would need to be doing in php to bring it all together (not code) just a quick rough guide on what I should be doing. I know I would need to do certain checks etc like check to ensure they have not referred them previously (for spam reasons) and that the email of person they want to refer has not been referred by someone else previously (for spam reasons) and that the email of person they want to refer does not exists as an already registered member in users table etc.

Maybe am I making this harder than what it actually is but I am for some reason not getting the logic behind it al, if someone could tell me if what I'm doing is totally wrong and not the best way to go about it please do.

I guess you could say I am asking for a mockup (draft) of a referrals table to do what I want and the sort of things I should be doing in php to bring it all together.

Answer 1

Set the referral table as follows:

referrals:
    id (primary key)
    referrer_id (user who is referring someone else)
    referred_email (email of person being referred)
    status (referred[default], registered)
    created_on
    updated_at

Following the policy "do not trust anybody" you should not use name as a field in referrals. Eg. I send a referral: email=>your_email, name=>abuse. It demotes you.

For counting no. of referrals:

select count(*) as referral_count from referrals where referrer_id = 'user_id'