存储推荐营销的分层数据（MySQL） [英] Storing Hierarchical Data (MySQL) for Referral Marketing

问题描述

我需要为注册到网站的用户设置一个5级层次结构。每个用户都被另一个用户邀请，我需要知道用户的所有后代。还有用户的祖先。

我想到了2解决方案。

1. 以这种方式保留关系表。关闭表：

  
 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 
  

1. 拥有此关系表。保持在一个表5级祖先。 祖先表格：

  
 user_id ancestor_level1_id ancestor_level2_id ancestor_level3_id ancestor_level4_id ancestor_level5_id 
 10 9 7 4 3 2 
 9 7 4 3 2 1 
  

我知道邻接表模型和修改的预订树遍历算法，但是这些是转荐系统的好解决方案吗？ p>

我需要在此树上执行​​的查询是：

• 新用户


• 当用户购买某物时，他们的推荐人会获得佣金百分比


• 每位用户应该能够了解他们

解决方案

关闭表

  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 
  

要添加由用户3引用的用户10.（我不会认为您需要在这两个插入之间锁定表格）：

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引荐的所有用户。

 从ancestor_table选择descendant_id，其中ancestor_id = 3; 
  

按深度对这些用户进行计数：

  select距离，count（*）from ancestor_table where ancestor_id = 3 group by distance; 
  

查找用户10的祖先。

  select ancestor_id，距ancestor_table的距离descendant_id = 10; 
  

此方法的缺点是此表将占用的存储空间量。

I need to have a 5 levels hierarchy for the users registered to a website. Every user is invited by another, and I need to know all descendants for a user. And also ancestors for a user.

I have in mind 2 solution.

1. Keeping a table with relationships this way. A closure table:

    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

1. Having this table for relationships. Keeping in a table 5 levels ancestors. A "ancestors" table:

   user_id ancestor_level1_id ancestor_level2_id ancestor_level3_id ancestor_level4_id ancestor_level5_id
   10      9                  7                  4                  3                  2
   9       7                  4                  3                  2                  1

Are these good ideas?

I know about "the adjacency list model" and "the modified preorder tree traversal algorithm", but are these good solutions for a "referral" system?

The queries that I need to perform on this tree are:

• frequently adding a new users
• when a user buys something, their referrers get a percentage commission
• every user should be able to find out how many people they've referred (and how many people were referred by people who they referred....) at each level

解决方案

Closure Table

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

To add user 10, referred by user 3. (I don't think you need to lock the table between these two insertions):

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);

To find all users referred by user 3.

select descendant_id from ancestor_table where ancestor_id=3;

To count those users by depth:

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

To find the ancestors of user 10.

select ancestor_id, distance from ancestor_table where descendant_id=10;

The drawback to this method is amount of storage space this table will take.

这篇关于存储推荐营销的分层数据（MySQL）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！