分销系统数据库设计篇
- 发表于
- 后端
分销项目，少不了分销者关联关系、订单关联、分销收益计算，这也是基础核心的部分。不同的需求会有不同的设计标准和要求，下面是一份分销系统核心数据库设计的示例可供参考：
分销用户表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE `fenxiao_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pub_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '公众号平台openid', `created_at` datetime NOT NULL, `status` tinyint(4) NOT NULL DEFAULT '1', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `taoke_code` varchar(255) NOT NULL DEFAULT '' COMMENT '淘客码', `mobile` varchar(255) NOT NULL DEFAULT '' COMMENT '手机号', `city` varchar(255) DEFAULT NULL, `avatar` varchar(255) NOT NULL DEFAULT '', `password` varchar(255) NOT NULL DEFAULT '', `nickname` varchar(255) NOT NULL DEFAULT '', `gender` tinyint(4) DEFAULT NULL, `mini_open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '小程序openid', `union_id` varchar(255) NOT NULL COMMENT '公共平台的union_id', `visit_code` varchar(255) NOT NULL COMMENT '分销邀请码', `visit_h5_link` varchar(255) NOT NULL, `vist_mini_qrcode` varchar(255) NOT NULL, `user_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1普通用户 2是分销用户', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10046 DEFAULT CHARSET=utf8mb4 |
简单讲下核心的几个字段
因为项目是基于微信生态的，所以主要推广平台为微信公众号h5 和微信小程序 , 其中三个字段 pub_open_id,mini_open_id,union_id 分别为微信公众号的 openid， 小程序的 openid 以及打通两者的开发平台上的联合 id。这样设计方便后期账户打通。
visit_code 字段是邀请码，可以在注册链接上带上用来识别用户之间的邀请关系。visit_h5_link 和 vist_mini_qrcode 分别是带有邀请码的 h5邀请链接以及邀请小程序码图片地址
taoke_code 字段是淘客链接生成需要的一个淘客邀请码，可以理解为 pid 广告位的一个东西
分销用户邀请关系表
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `fenxiao_visit_relation` ( `id` int(11) NOT NULL AUTO_INCREMENT, `visit_user_id` int(11) NOT NULL, `visited_user_id` int(11) NOT NULL, `level` varchar(255) NOT NULL COMMENT '邀请等级', `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1正常', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 |
用户关系表相对来说也不复杂，主要是来找到用户表 fenxiao_user 里的邀请关系。使用主表里的 visit_code 邀请码生成的小程序码或者 h5 邀请链接等注册登录后，可通过 visit_code 来找到当前注册用户的邀请者的 user_id，这里当前被邀请的用户 ID 是 visited_user_id, 发起邀请者的用户 ID 是 visit_user_id。
假设我们做了两级分销。当前注册的用户 C 的邀请者是 B，然后去查邀请表有 A 推荐 B 注册的，那么我们就可以在邀请关系表里面新增两条记录
|
1 2 3 |
|visit_user_id | visited_user_id| level | | B | C | 1 | | A | C | 2 | |
如上表 A 邀请过 B，B 再邀请 C，C 注册以后就有两条关于 C 的邀请关系，因为 B 是直接邀请，所以 level = 1 A 则是 C 的间接邀请者 level = 2。
通过上面的表就很容易的去查某个用户的下级以及下下级推荐关系。
分销订单表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE `fenxiao_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `order_status` tinyint(4) NOT NULL, `project_id` tinyint(4) NOT NULL, `pay_price` int(11) DEFAULT NULL, `order_id` varchar(255) DEFAULT NULL, `pay_time` int(11) DEFAULT NULL COMMENT '支付时间', `origin_profit` int(11) DEFAULT NULL, `profit` int(11) DEFAULT NULL COMMENT '订单返佣金额', `sms_title` varchar(255) DEFAULT NULL COMMENT '订单标题\n\n订单标题\n\n', `quantity` tinyint(4) DEFAULT NULL COMMENT '退款笔数', `refund_time` int(11) DEFAULT NULL, `refund_profit` int(11) DEFAULT NULL, `create_time` int(11) DEFAULT NULL, `pid` varchar(255) DEFAULT NULL, `promotion_rate` int(11) DEFAULT NULL COMMENT '给用户的比列', `origin_rate` int(11) DEFAULT NULL COMMENT '原始比列', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, `refund_price` int(11) DEFAULT NULL COMMENT '0', `project_type` int(11) DEFAULT NULL, `level` tinyint(4) NOT NULL DEFAULT '0', `origin_price` int(11) NOT NULL DEFAULT '0' COMMENT '原价', `order_user_id` int(11) NOT NULL COMMENT '下单人', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10333 DEFAULT CHARSET=utf8mb4 |
这里主要介绍下订单如何生成以及更新的方法
生成的时候往往是提交订单，可以利用 CPS 平台的订单回调通知方式 + 定时拉取 CPS 平台订单列表的方式来生成自己的分销订单表。
无论是回调通知还是订单列表都会有一个携带淘客推广为 pid 的一个参数，以及订单 ID。
然后就可以入库订单相关信息，同样的，我们可以记录订单到底是谁推广的，通过订单中的 pid 去查找到直接推广的UserID，然后通过 UserID 查找邀请关系表，分别按照自己设定的每一级别推广分佣比例 为这一系列用户分别生成一条订单记录，user_id 是产生订单收益的当前用户 ID，order_user_id 保存真正直接链接推广下单的用户 ID，同样要保存一下邀请表中的 level 订单列表可以展示出是下级还是下下级出的单。
依旧拿上面的邀请关系来说，C 的推广链接下的单
|
1 2 3 4 |
|user_id | order_user_id | level | profit | | C | C | 0 | 60%| | B | C | 1 | 20%| | A | C | 2 | 10%| |
通过上面的订单表，我们很容易知道有人通过用户 C 的推广链接下单，用户 A 的下下级推广出去成交的，所以可以获得该笔订单的 10% 推广费。
闭包表分销数据库设计
通过上面的示例，基本已经能看清分销数据表的设计关系，但感觉有些复杂？那来个通用经典的。
闭包表是解决分层存储一个简单而又优雅的解决方案，它记录了表中所有的节点关系，并不仅仅是直接的父子关系。
在闭包表的设计中，额外创建了一张TreePaths的表(空间换取时间)，它包含两列，每一列都是一个指向Comments中的CommentId的外键。
|
1 2 3 4 5 |
CREATE TABLE `Comment4` ( `id` int AUTO_INCREMENT , `words` varchar(255) NULL , PRIMARY KEY (`id`) ); |
父子关系表：
|
1 2 3 4 5 6 |
CREATE TABLE `RelaComment` ( `id` int AUTO_INCREMENT , `parentid` int not null , `childid` int not null , PRIMARY KEY (`id`) ); |
在这种设计中，Comments表将不再存储树结构，而是将书中的祖先-后代关系存储为RelaComment的一行，即使这两个节点之间不是直接的父子关系；同时还增加一行指向节点自己。
插入新节点
要插入一个新的叶子节点，应首先插入一条自己到自己的关系，然后搜索relacomment 表中后代是评论5的节点，增加该节点与要插入的新节点的"祖先-后代"关系。
|
1 2 3 4 5 6 7 |
insert into Comment4(words) values("iam 5") INSERT INTO relacomment(parentid,childid) SELECT t.parentid,5 FROM relacomment AS t WHERE t.childid = 4 UNION ALL SELECT 5,5 |
查询所有后代节点(查子树)
|
1 |
SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.childid WHERE t.parentid = 2 |
查询评论4的所有祖先(查祖先树)
|
1 |
SELECT c.* FROM comment4 AS c INNER JOIN relacomment t on c.id = t.parentid WHERE t.childid = 4 |
删除叶子节点
|
1 |
DELETE FROM relacomment WHERE childid= 5 |
删除子树
|
1 2 3 |
DELETE FROM relacomment WHERE descendant IN(SELECT childid FROM relacomment WHERE parentid = 2) |
要删除一颗完整的子树，比如评论4和它的所有后代，可删除所有在relacomment表中的后代为4的行，以及那些以评论4的后代为后代的行：
另外，闭包表还可以优化，如增加一个path_length字段，自我引用为0，直接子节点为1，再一下层为2，一次类推，查询直接自子节点就变得很简单。
闭包表是最通用的设计，并且最灵活，易扩展，并且一个节点能属于多棵树，能减少冗余的计算时间。但它要求一张额外的表来存储关系，是一个空间换取时间的方案。
总结
分销系统在市场中的使用已经非常广泛成熟，国内典型的PDD、美团、淘宝、京东的各种联盟、腾讯云、阿里云都是非常为成功的运用了分销系统，每每活动造势都会取得不错的效果，日常挂连接获取流量效果更佳。同时也有非常多的个人、团队，甚至公司专业就运营各大平台的分销代理，获得了不错的效果。
