Таблица закрытия

ancestor_id  descendant_id  distance
    1            1              0
    2            2              0
    3            3              0
    4            4              0
    5            5              0
    6            6              0
    2            3              1

Чтобы добавить пользователя 10, на который ссылается пользователь 3. (я не думаю, что вам нужно блокировать таблицу между этими двумя вставками):

insert into ancestor_table
select ancestor_id, 10, distance+1
from ancestor_table
where descendant_id=3;

insert into ancestor_table values (10,10,0);

Чтобы найти всех пользователей, которых привел пользователь 3.

select descendant_id from ancestor_table where ancestor_id=3;

Чтобы подсчитать этих пользователей по глубине:

select distance, count(*) from ancestor_table where ancestor_id=3 group by distance;

Чтобы найти предков пользователя 10.

select ancestor_id, distance from ancestor_table where descendant_id=10;

Недостатком этого метода является объем памяти, который займет эта таблица.

Используйте механизм хранения OQGRAPH.

Возможно, вы захотите отслеживать произвольное количество уровней, а не только 5 уровней. Получите один из форков MySQL, который поддерживает движок QGRAPH (например, как MariaDB или OurDelta) и используйте его для хранения своего дерева. Он реализует модель списка смежности, но, используя специальный столбец с именем latch для отправки команды механизму хранения, сообщая ему, какой запрос выполнять, вы получаете все преимущества замыкающей таблицы без необходимости выполнять бухгалтерскую работу. каждый раз, когда кто-то регистрируется на вашем сайте.

Вот запросы, которые вы бы использовали в OQGRAPH. См. документацию по адресу http://openquery.com/graph-computation-engine-documentation.

Мы собираемся использовать origid в качестве реферера и destid в качестве реферера.

Чтобы добавить пользователя 11, привлеченного пользователем 10

insert into ancestors_table (origid,destid) values (10,11)

Чтобы найти всех пользователей, которых привел пользователь 3.

SELECT linkid FROM ancestors_table WHERE latch = 2 AND origid = 3;

Чтобы найти предков пользователя 10.

SELECT linkid FROM ancestors_table WHERE latch = 2 AND destid = 10;

Чтобы найти количество пользователей на каждом уровне, переданных пользователем 3:

SELECT count(linkid), weight
FROM ancestors_table
WHERE latch = 2 AND origid = 3
GROUP BY weight;

Управление иерархическими данными в MySQL

В целом мне нравится "вложенный набор", особенно. в MySQL, который на самом деле не имеет языковой поддержки иерархических данных. Это быстро, но вам нужно убедиться, что ваши разработчики прочитали эту статью, если простота обслуживания имеет большое значение. Это очень гибко - что, похоже, не имеет большого значения в вашем случае.

Кажется, это хорошо подходит для вашей проблемы - в реферальной модели вам нужно найти дерево рефереров, которое быстро работает в модели вложенного множества; вам также необходимо знать, кто является ~детями@ данного пользователя, и глубину их отношений; это тоже быстро.

Строка предков с разделителями

Если вы серьезно рассматриваете 5-уровневую таблицу отношений, это может упростить использование строки предков с разделителями вместо 5 отдельных столбцов.

user_id  depth   ancestors
10       7       9,7,4,3,2,1
9        6       7,4,3,2,1
...
2        2       1
1        1       (empty string)

Вот некоторые команды SQL, которые вы могли бы использовать с этой моделью:

Чтобы добавить пользователя 11, привлеченного пользователем 10

insert into ancestors_table (user_id, depth, ancestors)
select 11, depth+1, concat(10,',',ancestors)
from ancestors_table
where user_id=10;

Чтобы найти всех пользователей, указанных пользователем 3. (Обратите внимание, что этот запрос не может использовать индекс.)

select user_id
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3';

Чтобы найти предков пользователя 10. Вам нужно разбить строку в вашей клиентской программе. В Ruby код будет ancestorscolumn.split(",").map{|x| x.to_i}. В SQL нет хорошего способа разбить строку.

select ancestors from ancestors_table where user_id=10;

Чтобы найти количество пользователей на каждом уровне, переданных пользователем 3:

select
   depth-(select depth from ancestors_table where user_id=3),
   count(*)
from ancestors_table
where ancestors like '%,3,%' or ancestors like '3,%' or ancestors like '%,3'
group by depth;

Вы можете избежать атак SQL-инъекций в like '%,3,%' частях этих запросов, используя вместо этого like concat('%,', ?, ',%') и привязывая целое число для номера пользователя к заполнителю.