I need to make a simple referral system and I have a database table set out as follows:

mem_id | date | refer_email | accepted 

And the meaning of such columns is the following:

  • mem_id: The member unique identifier who is making the referral.
  • date: The timestamp value of the request.
  • refer_email: The contact email address owned by the person being referred.
  • accepted: A CHAR value of Y or N that indicates if the referred person received acceptance.

I am not sure if this is best structure for this kind of scenario. I will be required to tally the number_of_users referred by each person mainly, and I expect a few more simple queries on the horizon.

  • So when accepted is Y, the referred person has been accepted. What does it mean when it is N? Rejected? Would there be a need to represent if a referred person has neither been accepted nor rejected? – Joe Borysko Jun 8 '15 at 23:16
  • There is a user table as well, I shall post it. Yes if the referral is rejected I would like to keep track of that as well. – kabuto178 Jun 10 '15 at 3:53

I would add the following to that schema:

refer_mem_id - The member unique identifier of the person who has accepted the referral.

Remove the accepted column, the value in refer_mem_id gives you this information.

  • I see, so I would update this column after the person has registered in this case? – kabuto178 Oct 9 '15 at 7:23
  • @kabuto178 Yes that is correct – malpaso Nov 11 '15 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.