Databases
StackOverflow2013
Postsdbo
PODetermining "Number of Users below" in a multi-tier member database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODetermining "Number of Users below" in a multi-tier member database
    primarykey
    data
    text
    <p>I've programmed a membership site for a client within which members join <em>below</em> other users. e.g.</p> <pre><code>userid | name | subof 1 | John | 0 2 | Joe | 1 3 | Jill | 0 4 | Janet | 2 5 | Juan | 1 6 | George| 2 </code></pre> <p>John and Jill are at the top, Joe and Juan are below John, and Janet and George are below Joe. The tier-ing is used for passing up commission. My client wants to be able to see How many users are below any given user, <em>(at least it's restricted to 8 tiers out)</em></p> <p>For now I have added the additional field `num_below` to the user table, and that field is incremented or decremented whenever someone joins or leaves below the user.</p> <p>The first problem with this is that it feels like it violates good Database Normalization practices~ cuz it's storing data that's already in the DB</p> <p>The second is that it get's <strong>hairy</strong> when my client comes and says "Oh, George meant to join under Juan, please move him"</p> <p>I considered just dynamically calculating the number below every time it's asked for, but the db queries would seem to grow exponentially.</p> <p>I've written a <code>rectifySubs()</code> function that can go through and fix all of the `num_below` fields, but as there are more members it will become more and more intensive to run~</p> <pre><code>function rectifySubs(){ $NumBelow=array();//UID=&gt;NUM_BELOW $SubOf=array();//UID=&gt;IS_A_SUB_OF_UID $Uids=array();//UID $r=mysql_query("SELECT uid,subof FROM user"); if(!$r || mysql_num_rows($r)==0){return 'Invalid';} while(list($uid,$subof)=mysql_fetch_row($r)){ $NumBelow[$uid]=0; $SubOf[$uid]=$subof; $Uids[]=$uid; } mysql_free_result($r); $RungsUp=8; foreach($Uids as $uid){ $r=1; $parent=$SubOf[$uid]; while($parent&gt;0 &amp;&amp; $r&lt;=$RungsUp){ $NumBelow[$parent]+=1; $parent=$SubOf[$parent]; $r++; } } $QueryByNum=array(); foreach($NumBelow as $uid=&gt;$num){ if(!isset($QueryByNum[$num])){$QueryByNum[$num]=array();} $QueryByNum[$num][]=$uid; } unset($QueryByNum[0]); mysql_query("UPDATE user SET below=0"); foreach($QueryByNum as $num=&gt;$uids){ $where=$or=''; foreach($uids as $uid){ $where.=$or."`uid`=".$uid; $or=" OR "; } mysql_query("UPDATE user SET below=".$num." WHERE ".$where); } } </code></pre> <p>Any recommendations? I don't want to put too much redundant data in the DB, but going 8 tiers out every time seems way too processor intensive.</p> <p>-- EDIT --</p> <p>I wasn't clear enough about how the tiers worked so I made the table bigger. The key issue I'm addressing with the edit is that any person can have <strong>multiple</strong> people in a tier directly below them. Hope that makes sense.</p> <p>-- SOLUTION -- (Implementation of Kakao's solution as a method of the 'Member' Class)</p> <pre><code>protected function getNumBelowAtLevel($i=1,$force=false){ $i=abs((int)$i); if($i&lt;=1){return 0;}//Level 1 is just the member themselves if($force || !isset($this-&gt;numBelow[$i])){ $Us=''; $Sels=''; $Lefts=''; $Groups=''; $comma=''; $nl=''; for($k=1;$k&lt;=$i-1;$k++){ $j=$k==1?'0':$k-1; $Us.=$comma.'u'.$k; $Sels.=$comma.$nl.'m'.$k.'.mid as u'.$k; $Lefts.=$nl.'left join members as m'.$k.' on m'.$k.'.subof = m'.$j.'.mid'; $Groups.=$comma.'u'.$k; $nl="\n\t\t\t\t\t"; $comma=', '; } $sql="select count(*) - 1 as users_below from ( select distinct {$Us} from ( select {$Sels} from members as m0 {$Lefts} where m0.mid = {$this-&gt;id} group by {$Groups} with rollup ) d ) a"; if(DEBUG){var_dump($sql);} $r=mysql_query($sql); list($this-&gt;numBelow[$i])=mysql_fetch_row($r); } return $this-&gt;numBelow[$i]; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShad
    1. USShad
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. This table or related slice is empty.
      1. VTModeratorReview
    3. VO
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. USClodoaldo Neto
      1. VTFavorite
    1. COHow many employees are there? If only a few dozen, the exponential behavior won't hurt anything. Though, it shouldn't be exponential, as you only have to query at a max of 8 times to find everyone under any given employee.
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. USerisco
    2. CO@erisco thanks for your interest. It's not employees, it's members, and the owner wants it to go viral~ i.e. friends getting their friends to join getting their friends to join on and on and on~
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. USShad
    3. CO@mu thanks for the tip! I'm going to dig in that direction.
      singulars
      1. PODetermining "Number of Users below" in a multi-tier member database
      1. USShad
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload