我需要为注册到网站的用户提供5级层次结构 . 每个用户都被另一个用户邀请,我需要知道用户的所有后代 . 也是用户的祖先 .
我想到了2个解决方案 .
- 以这种方式保持关系表 . 关闭表:
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
- 拥有此表用于关系 . 保持在5个级别的祖先表中 . 一个"ancestors"表:
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
这些好主意吗?
我知道“邻接列表模型”和“修改后的预订树遍历算法”,但这些是“推荐”系统的良好解决方案吗?
我需要在这棵树上执行的查询是:
-
频繁添加新用户
-
当用户购买东西时,他们的推荐人获得百分比佣金
-
每个用户应该能够在每个级别找出他们推荐了多少人(以及他们推荐了多少人......)
4 回答
关闭表
添加用户3引用的用户10.(我认为您不需要在这两个插入之间锁定表):
查找用户3引用的所有用户 .
要按深度计算这些用户:
找到用户10的祖先 .
此方法的缺点是此表将占用的存储空间量 .
使用OQGRAPH存储引擎 .
您可能希望跟踪任意数量的级别,而不仅仅是5个级别 . 获取一个支持QGRAPH engine的MySQL分支(例如MariaDB或OurDelta),并使用它来存储您的树 . 它实现了邻接列表模型,但通过使用一个名为
latch
的特殊列向存储引擎发送命令,告诉它要执行什么样的查询,您可以获得闭包表的所有优点而无需进行簿记工作每次有人注册您的网站 .以下是您在OQGRAPH中使用的查询 . 请参阅http://openquery.com/graph-computation-engine-documentation上的文档
我们将使用origid作为引用者,并将destid用作引用 .
添加用户11引用的用户11
查找用户3引用的所有用户 .
找到用户10的祖先 .
要查找用户3引用的每个级别的用户数:
分隔祖先的字符串
如果您正在强烈考虑5级关系表,则可能会简化使用分隔的祖先字符串而不是5个单独列的事情 .
以下是您在此模型中使用的一些SQL命令:
添加用户11引用的用户11
查找用户3引用的所有用户 . (请注意,此查询不能使用索引 . )
要查找用户10的祖先 . 您需要在客户端程序中分解字符串 . 在Ruby中,代码是
ancestorscolumn.split(",").map{|x| x.to_i}
. 在SQL中分解字符串没有好办法 .要查找用户3引用的每个级别的用户数:
您可以通过使用
like concat('%,', ?, ',%')
而不是将用户编号的整数绑定到占位符来避免这些查询的like '%,3,%'
部分中的SQL注入攻击 .Managing Hierarchical Data in MySQL
一般来说,我喜欢“嵌套”,尤其是在MySQL中,它实际上没有对分层数据的语言支持 . 这很快,但如果易于维护是一件大事,你需要确保你的开发人员阅读那篇文章 . 它非常灵活 - 在您的情况下似乎并不重要 .
它似乎很适合你的问题 - 在推荐模型中,你需要找到引用树,它在嵌套集模型中很快;你还需要知道谁是给定用户的〜儿童@,以及他们关系的深度;这也很快 .