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: table: 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: id ref1 ref2 ref3 etc 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 insight? __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
