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 (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to