traversal tree / referal system

[I-AM-OBODO]

hi guys.

i want to implement a referral system. a member can only refer using a form in the memebers area. on the referral form, i have a hidden field which is his referral id and a unique code (md5 generated link) and a field for the email address he wants to refer. once he sends the mail, a referal link (md5 generated, for uniqueness and to curb cheating) is sent to the email so that once he clicks, it takes him/her to the registration area.


on my database table

referrer_uid - the unique id of person referring someone
referred_uid - the unique id of person being referred
ref_code - the md5 generated code which is used on the link sent
referred_email - the email address of the person being referred
status - once a member sends a refer link, status is set to Referal sent
date_referred - the date the refering took place
date_accepted - the day the referred was accepted (i dont want this to change on clicking a referal link or registration cos not everyone gets accepted to avoid spurious referals)


my question is how do i create a traversal tree with this. i read about traversal tree on sitepoint but when i tried to implement the examples, it didnt work for me and i noticed it was an old post.

note: i intend giving points based on the child/tree and it could be as deep as possible. also if my method is wrong, ideas are higly appreciated.

thanks
 

[hansford]

Assuming I understand the question...you have members who refer other people who themselves become members..

 

You have a "Members" table which holds the member ID's and date & time of when they became members.

 

Members

------------------

Member_Id

name

email

etc 

etc

Date_Time

 

You have the "Referrals" table which holds the Member Id of the now member and the Referrer Id of who referred them and the date & time of when they were referred or actually signed up.

 

Referrals

-------------------

Member_id

email

etc

etc

Referer_Id

Date_Time

 

You can traverse by following a member -> any members that have a row/s in the referral table ->

RECURSION 

 

I'll step aside and see what the senior members have to say. It appears like a recursion problem to me at first glance.

 

[kicken]

Switching your database to a nested set model may be beneficial. It makes it easier to query for things like the path to the root (ie, referral path) or tree depth (how many levels of referrals a person has).

 

It's a little more complex to manage and maintain, but tends to make working with the data more efficient and sometimes easier.

 

It may help to explain more specifically what kind of things you'd like to do. Like for example you say you want to award points. How exactly do you want to award points?

Edited December 12, 2014 by kicken

[I-AM-OBODO]

Switching your database to a nested set model may be beneficial. It makes it easier to query for things like the path to the root (ie, referral path) or tree depth (how many levels of referrals a person has).

 

It's a little more complex to manage and maintain, but tends to make working with the data more efficient and sometimes easier.

 

It may help to explain more specifically what kind of things you'd like to do. Like for example you say you want to award points. How exactly do you want to award points?

How exactly do you want to award points?

for instance I give 4 points on first tree and 3points on second tree, 2points on third tree and above.

[I-AM-OBODO]

Switching your database to a nested set model may be beneficial. It makes it easier to query for things like the path to the root (ie, referral path) or tree depth (how many levels of referrals a person has).

 

It's a little more complex to manage and maintain, but tends to make working with the data more efficient and sometimes easier.

 

It may help to explain more specifically what kind of things you'd like to do. Like for example you say you want to award points. How exactly do you want to award points?

Hi kicken. I've read the link you sent, i think changing to a nested set model will do the job, but how can i switch my database to a nested set model with my given fields or any other better way. Thanks

[I-AM-OBODO]

Switching your database to a nested set model may be beneficial. It makes it easier to query for things like the path to the root (ie, referral path) or tree depth (how many levels of referrals a person has).

 

It's a little more complex to manage and maintain, but tends to make working with the data more efficient and sometimes easier.

 

It may help to explain more specifically what kind of things you'd like to do. Like for example you say you want to award points. How exactly do you want to award points?

 

Hello.

 

The problem I'm having with the link you sent me is, how do they determine the left and right values? since i cannot just insert any value to them.

 

thanks