Unsolvable problem with mysql table design. Tracking referrals

jbh

Thanks for your time. I am having a horrible time with this

I am trying to figure out the smartest way to
track member referrals for a free service of mine.

See, it is 3 levels. It means that if Jane signs up
and she refers Bill. And Bill refers Sally. And Sally refers
Joe,, then Joe, Bill, Sally all earn points for Jane. Make sense?

However, if I add them to a 'referral' table in a database
I do not know if I should create a separate row
for Joe and Bill, as they refer somebody too. That is the dilema

Is that the smartest way to track 3 way referrals?

And if Jane signs up 33 people, it's really 11 rows
of 3 referrals for her in the table?

What is the best way to set this up? I feel I am complicating things and thinking too much. And that this will create a gigantic table over time. What is the smartest way to design this table?

Cinquecento

....ok here goes......

I reckon you only need 1 referral table....

with three columns - referID (key), referee,referer.

You can track as many levels as you like - by left joins -:

referer | referee
Jane Bill
Bill Joe
Joe Sally

so your query would find Jane by aliasing 3 times from the same table?

I had something like this for cross referencing spares and accessories to products - the query had to work either way from just one table...

Any good?

jbh

Interesting...here is what I did...as I must only use 3 levels. No more or less...

I used a table such as:

Referrer | Ref1 | Ref2 | ref3

If I refer

Jack/Fred/Dave

the table can look like

Me | Jack | Fred | Dave

But what do I do about Jack?

Create another row when he is referring somebody?

Jack | Fred | Dave

And does that mean Fred has his row as well?

In other words, when I refer Bob, and Bob Refers Jake
does the table look like this?

Me | Bob | Jake
Bob | Jake

jbh

how would I use the left join option if I used your method, to make sure I am only going 3 levels down?

m_tt

    ##	Half-assed idea... let PHP do all the work

+-------+-----------+-----------+---------------+
|  ID   |  Referer  |   Total   |   Referees    |
+-------+-----------+-----------+---------------+
|   1   |  Jack     | 3         | Bill,Bob,Jane |
|   2   |  Bob      | 2         | Jack,Joe      |
+-------+-----------+-----------+---------------+

## You could always use ID #'s:
## This would mean that jack refered 51,53,54 which
## is Bob,Jane, and Joe. And Bob refered 63,62 which
## would be whoever #'s 63 and 62 are. Get it?

+-------+-----------+-----------+---------------+
|   ID  |  Referer  |   Total   |   Referees    |
+-------+-----------+-----------+---------------+
|   50  |  Jack     | 3         | 51,53,54      |
|   51  |  Bob      | 2         | 63,62         |
|   53  |  Jane     | 3         | 76,28,91      |
|   54  |  Joe      | 2         | 82,83         |
+-------+-----------+-----------+---------------+

I don't know.. i'm bad at organizing databases 🙂

Cinquecento

OK - here is table called 'referrals'

+---------+-----------+-----------+
| referID | referee | referred |
+---------+-----------+-----------+
| 1 | Jane | Bob |
| 2 | Bob | Grimble |
| 3 | Grimble | Bobe |
| 4 | Bobe | Grenville |
| 5 | Grenville | Gladys |
| 6 | Grimble | Kate |
| 7 | Kate | Bilbus |
| 8 | Bilbus | Spong |
+---------+-----------+-----------+

Here is query....

select original.referee from referrals AS original left join referrals AS second ON original.referred = second.referee LEFT JOIN referrals as third ON second.referred = third.referee WHERE third.referred = 'Spong';

+---------+
| referee |
+---------+
| Grimble |
+---------+

Pass 'Bobe to the query - you get Jane!

Pass 'Gladys' - you get Grimble!

Pass 'Grimble' - you get nothing - 'cos he doesn't have a 'third' referee....

Does this do it?

Ian

jbh

I have to try that. I never used such advanced queries
so I am unsure of how it would work

I trust this means that even if a user refers 40 people
there is a way to query all of the users that are 3 levels below

Is this the case? I can try it out

However, what does 'pass' refer to?

I am not used to such advanced queries, hence, I have to ask these silly q's

Thanks

Cinquecento

Yes - it will only ever return the THIRD referee..

I didn't know how to do this until a month ago - thanks to the people that help out here it's (becoming) clearer!

The best thing to do is look through the query backwards - then you can see that the query finds a match three times where the referee is the same as the referred.... and it looks at the same table THREE times but each time gives it a 'nickname' to clarify what matches with what...?

When I say PASS a value - it's the value of the WHERE clause at the end....

Your query will say....

where $userName is the person for whom you want to find the 'third' level of referrer...

Phew!

Ian

jbh

Thanks Ian, however, one thing confuses me

I am not looking for the 3rd level by itself. (Although I MIGHT be misunderstanding this lol. I have to test it...)

I just want to make sure that I am able to
confirm at any point that my query would know who the 3 levels of referrals are for somebody

Such as
Dan refers Bill = > Jim = > Jane

Those 3 are under Dan, right?

Then let's say Dan refers 20 more people

Each have 2 people they refer

Those people also are under Dan, 3 levels down

A time will come where I want to be able to track when a referred person, up to 3 levels down from 'Dan' is earning points

So that's why I created

Referrer | Ref 1 | ref 2 | ref 3
Dan Bill Jim Jane

My question was when Bill is referred, do I add a new row for him
such as

Referrer | Ref 1
Bill Jim

And then create a new row for Jim when he finally refers jane?

See, getting the 3rd level by itself isn't needed
I just want to be able to query so I can find out all those that
are 3 levels deep for Dan, and then calcuate their points. See, my dilema is not so much adding referral 1/2/3 for Dan, but what to do for Referral 1, such as adding him to a new row for himself as well at the same time

Does that make sense? Please don't hate me lol I am EXTREMELY THANKFUL for your time. It is MY FAULT if I can't explain this properly

Cinquecento

Sorry - busy this morning, but I'll try and help later on!

Ian

jbh

Thanks, I appreciate it 🙂