I have a question regarding tracking multiple referral levels in a database. The number of referral levels tracked needs to be at least 4, but should be able to be expanded later (without modifying the database).
The first design I considered was: table: id int(8) unsigned not null auto_increment, referer int(8) unsigned null, primary key (id), key tbl_referer(referer)); What I need to be able to do is give a breakdown of the # of members referred (on each level) for a specific member...say, member 10. Getting the # of referrals on level 1 is no problem: SELECT COUNT(*) FROM table WHERE id = 10 The second level isn't too tough either, using a simple join. But what about when I get down to level 4? Is it even possible to get this info in one query? How about level 10? The only other thing I can think of doing is storing not just the referer in the table, but something like: id ref1 ref2 ref3 ref4 ref5 etc... All of the logic would need to be taken care of during member registration, and querying to find the number of referrals on any given level for a member would be simple. However, this method does not allow for easy expansion of referral levels, which is what I want, and is probably not the best way of doing things. Can anyone offer any insight? __________________________________________________ Do you Yahoo!? New DSL Internet Access from SBC & Yahoo! http://sbc.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
