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) id referer 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 = people.id 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 To: [EMAIL PROTECTED] 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: 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
