Database design for affiliate system

Question

i am building an affiliate system and i have stacked with the design of the database in one part. I dont know how to keep track on that who recruited whom. Lets say we have something like this:

• Jon smith
  • Alex Del
    • Jim West
    • Marko Polo
    • Johny Bravo
  • Pit Sampras
    • Sara Mat
    • Gabriella white
    • Antonio Santo
  • Maja Grozdanova
    • Agon Xheladini

All this goes this way: 1.Jon recruited Alex, Alex Recruited Jim, Marko and Johny. 2.Jon recruited Pit, Pit Recruited Sara, Gabriella and Antonio. 3.Jon recruited Maja, Maja recruited Agon. Agon can recruit some one ells and that one can recruited someone ells, this goes in infinite depth.

Does any one has an idea how to solve this problem. How to design tables ?

Answer 1

• One table for "entities" you deal with (people, businesses)
• One table "EntityRelation" determining relations

So, Alex Dell has a relation to JonSmith that he hired him. This is a pretty standard CRM approach.

Answer 2

Just use a self referencing table, with an ID and a recruitedbyid:

ID  Name       RecruitedBy
1   Jon Smith  null
2   Alex Del   1
3   Marko Polo 1
...

You did not mention which database you are using, but SQL Server 2008 has a hierarchical data type suited for just these situations, hierarchyid.

Answer 3

Create two columns:

• int userId: PRIMARY KEY and AUTO_INCREMENT if your database supports it. Each user will be assigned a unique number.
• int referrer: This is the userId of the person who referred the current user. It can be set to 0 or NULL if the user is not an affiliate of any other user.

Tree diagrams can be easily made with a few lines of code.

EDIT: Since you asked, here's some code for a tree diagram. I chose PHP because MySQL queries are easy to implement, but the concept can easily be done in Java, C, Ruby, Python, etc.

function listUsers($handler, $id, $prefix) {

    // Please use MySQLi extension with prepared statements or your code
    // becomes SQL injection paradise
    $results = mysql_query("SELECT `user`, `referrer` FROM `users` WHERE `referrer` = $id");

    while ($row = mysql_fetch_row($results) {
        echo $prefix . $row[0];
        listUsers($handler, $$row[1], $prefix . "* ");
    }
}


$handler = mysql_connect(. . .);
listUsers($handler, 0, "")

I'm 80% sure the logic will work when implemented correctly. It should return:

/*
Jon smith
* Alex Del
* * Jim West
* * Marko Polo
* * Johny Bravo
* Pit Sampras
* * Sara Mat
* * Gabriella white
* * Antonio Santo
* Maja Grozdanova
* * Agon Xheladini
*/