niceyoo博客 niceyoo博客

网站三级分销数据库如何设计,简单案例

in JavaWeb 文章转载请注明来源!

一、问题产生

有小伙伴微信私信我,说老板想设计一套三级返佣的微信淘宝客裂变系统,然后问我怎么搞...

咳咳,对于三级分销的数据库设计,相信很多小伙伴头疼的可能不是设计上,而是查询上,因为通常涉及到会员分级,那么涉及到的查询可能有且不局限于:一二级用户列表混合查询、统计今日注册一二级用户、统计本月注册一二级用户,如果涉及到了金额,那么可能还会有:查询一二级充值订单、查询一二级充值返佣金额等等等等。

首先我们来想一下,对于这种有上下级关系的表如何设计呢?

通常部门树是不是符合这个逻辑呢?我曹还别说,上下级部门表还真有那么点意思,我们来看一下这个表结构:

dept_iddemp_namedept_parent_id
部门id部门名称父级(上级)部门id

我们就按这个部门表的设计套进用户表看看:

user_id(用户id)user_name(用户名称)user_parent_id(父级用户id)
1小明0(假设小明就是一级)
2小红1
3小王2
4小刘3
5小张4

现在的用户数据等级关系为:( > 表示推荐)

小明 > 小红 > 小王

小王 > 小刘 > 小张

如果我想查询 小明 的二三级用户,那么就需要通过sql语句:

select * from user where user.user_parent_id = '1' ...

不对不对,这样查询出来查到的是 小明 的二级用户,想要查询三级用户的话,就需要用到子查询了,那么对于这种情况在部门表是怎么解决的呢?

递归调用... 是的,递归调用,因为本身部门表的数据肯定是有限的,所以用这种方式查询再加上缓存技术,这点查询性能问题是完全可以忽略的了,但是用户表如果这么设计就可以辞职回家了,怎么办呢?

然后在接下来的讨论过程中,小伙伴就说,怎么不给用户表加一个等级标识呀,就是再加一个字段,比如 小红小明 的二级,那么这个这个字段就标记为2,小王小明 的三级,那么就标记为3,这样查询不就好查询了吗。

还别说,听上去感觉可以,但是,如上数据 小王小明 的三级,但是 小王 同样也是 小张 的一级,那么怎么标呢?

所以最终的问题就是,使用部门树这种情况,区分或者查询二三级用户比较困难。

二、尝试解决

其实后来小伙伴们提到的增加等级标识已经有那点意思了~

我们接下来将这个User表跟这个等级标识进行拆分一下:

用户表(user):

user_iduser_nameuser_parent_id
用户id用户名称父级用户id

用户关系表(user_relation):

iduser_parent_iduser_children_iduser_level
关系表主键父级用户id子级用户id用户等级

简单说明一下用户关系表的主键,设计上实际没什么作用,但是仍加上这个自增主键是因为,对于InnoDB存储引擎来说,如果当前表没有主键,那么会默认生成一个隐藏列作为自增主键,所以怎么都是生成主键,还不如自己指定一个主键,显式主键可以提高查询效率。(面试小技巧,Get到了吗)

通过如上两个表我们就可以很简单的区分上下级关系了,我们套入上边的数据看一下:

小明 > 小红 > 小王

小王 > 小刘 > 小张

iduser_parent_iduser_children_iduser_level
11(小明)2(小红)1(一级)
21(小明)3(小王)2(二级)
32(小红)3(小王)1(一级)
43(小王)4(小刘)1(一级)
53(小王)5(小张)2(二级)
64(小刘)5(小张)1(一级)

通过这种模式,是不是就清晰多了呢,感觉能分好多级啊,如果你想弄个四级分销,咳咳,亲,我这边建议您赶紧离职(国家规定超过三级就算违法)。

那么我们现在再来看看数据的查询:

三、相关查询

一级下级查询:(小明的一级用户为例)

select * from user_relation t where t.user_parent_id = '1' and user_level = '1'

二级用户查询:(小明的二级用户查询)

select * from user_relation t where t.user_parent_id = '1' and user_level = '2'

一二级用户列表混合查询:(小明的一二级用户查询)

select * from user_relation t where t.user_parent_id = '1'

对于当前用户查询时 user_id 肯定是明确的了,大家可以自行关联 user 用户表详细信息

统计今日注册的一级用户:(小明的一级用户为例)

select count(id) from user_relation relation where t.user_parent_id = '1' and user_level = '1' and relation.create_time = curdate()「mysql日期函数」

统计今日注册的二级用户:(小明的二级用户为例)

select count(id) from user_relation relation where t.user_parent_id = '1' and user_level = '2' and relation.create_time = curdate()「mysql日期函数」

如果涉及到金额,或者订单呢?

先上订单表(精简版):(user_orders)

order_id(订单id)user_id(用户id)amount(消费金额)
12(小红)5元
23(小王)3元
33(小王)4元
.........

查询小明一二级用户订单返佣金额(假设一级用户返佣10%,二级用户返佣5%):

SELECT
    sum(
         decode(relation.user_level,1,orders.amount*0.1,2,orders.amount*0.05) 
    ) AS rebate_amount
FROM
    user_relation relation,
    user_orders orders
WHERE
    orders.USER_ID = relation.CHILDREN_USER_ID
    AND relation.user_parent_id = '1'
    and relation.user_level > 0

好了,到这罗列了几个一二级的简单查询,更多的查询大家自行根据场景需要编写。

博客园持续更新呀,欢迎大家订阅关注,一起成长。

本文首发于博客园:https://www.cnblogs.com/niceyoo/p/13614975.html

jrotty WeChat Pay

微信打赏

jrotty Alipay

支付宝打赏

文章二维码

扫描二维码,在手机上阅读!

本文基于《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权
文章链接:https://sscai.club/index.php/archives/129/ (转载时请注明本文出处及文章链接)

数据库设计
发表新评论
前篇 后篇
雷姆
拉姆