For performance sake I'd recommend having a table configured just like your first 
example minus referer as well as a seperate table that is:

(table references)
int level

Anytime you add a new user you climb the chain until you hit the top of the chain, for 
each user you pop in an id, referer combination. Level would be how far away the user 
is or which recursion you're on. That way to get a list of referers within N 
generations you would say:

select * from references left join people on references.referer = where id = 
thePersonIAmLookingFor and level <= N

This does mean all inserts must occur in this way or you would have to process the 
existing data to create the references table.
... there may of course be a better way. :-)

<>< Ryan

-----Original Message-----
From: Daren Cotter [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 25, 2002 12:08 PM
Subject: [PHP-DB] Multiple Referral Levels

I'm using MySQL to attempt to track multiple referral
levels for a web site. I.e., member 1 refers 2, 2
refers 3, 3 refers 4...member 4 is is on member 1's
3rd referral level.

Anyway, I need to be able to compile a list of the #
of referrals for a specific member on any given level
(up to 12 levels), and I'm trying to determine the
best database setup. I know that a normalized setup
could be:

id int unsigned not null auto_increment
referer int unsigned null
primary key (id)
key (referer)

With this setup, inserting new members would be no
problem. Grabbing the number of referrals for a member
on level 1 would be easy:
SELECT count(*) FROM table WHERE referer = 1
Second level wouldn't be too difficult either, using a
join, but 3rd level and on would be done how? Is it
even possible? I'm aware I could setup some sort of
recursive function, but if a member has 5,000
referrals on level one, I certainly don't want to run
5,000 queries and total the results to get level 2.

So perhaps a design more like:
would be better? It's not normalized, but it might do
the job...querying any member's referrals for a
specific level would be no problem. The signup process
would have a bit more work to do, because it'd have to
recursively find the referer all the way up of course.

Bottom line, is I'm looking for the best database
setup that will accomodate my needs (I NEED to be able
to run a queyr that shows me the # of referrals broken
down by level for a member).

Ideally, the # of referral levels would be variable,
meaning the database should not be setup in a way to
accomodate x referral levels, but instead should
accomodate 0-12.

I know there are websites that achieve this (whether
they use MySQL, I don't know)...can anyone offer any

Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!

PHP Database Mailing List (
To unsubscribe, visit:

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to