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 ?

Oded · Accepted Answer · 2010-07-30T07:57:23.643

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.

edited Jul 30 '10 at 07:57

answered Jul 30 '10 at 07:42

Oded

489,969
99
883
1,009

sorry for that i am using MySQL – AXheladini Jul 30 '10 at 07:58
This will be the general table of users or i will need to create another table only for this issue ? – AXheladini Jul 30 '10 at 07:59
@AXheladini - this would be the only table, as your requirement seems to be that any user can be a recruiter. Users that have not been recruited will have a `null` for RecruitedBy. – Oded Jul 30 '10 at 08:00
I see, do you think this is an good approach for an affiliate system ? – AXheladini Jul 30 '10 at 08:02
@AXheladini - it is a standard approach for a hierarchical users table. – Oded Jul 30 '10 at 08:05

score 1 · Answer 2 · answered Jul 30 '10 at 07:42

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.

answered Jul 30 '10 at 07:42

TomTom

61,059
10
88
148

Vortico · Answer 3 · 2010-07-30T14:49:23.720

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
*/

Sorry about that. I have made a mistake in the query and $row[1]. It's fixed now, so it should work. — Vortico, Jul 30 '10 at 14:50

Database design for affiliate system

3 Answers3